INTERVAL

Description

An INTERVAL object represents duration or amount of time, without referring to any specific point in time. There is no equivalent in Snowflake so it is transformed to Varchar (BigQuery Language Reference INTERVAL Data Type)

There is no equivalent for this data type in Snowflake, it is currently transformed to VARCHAR.

INTERVAL int64_expression datetime_part

INTERVAL datetime_parts_string starting_datetime_part TO ending_datetime_part

Sample Source Patterns

Interval with a single DateTime part

BigQuery

Query

SELECT INTERVAL 1 YEAR;

SELECT CURRENT_DATE + INTERVAL 1 YEAR, 
  CURRENT_DATE + INTERVAL 1 QUARTER,
  CURRENT_DATE + INTERVAL 1 MONTH,
  CURRENT_DATE + INTERVAL 1 WEEK,
  CURRENT_DATE + INTERVAL 1 DAY,
  CURRENT_DATE + INTERVAL 1 HOUR,
  CURRENT_DATE + INTERVAL 1 MINUTE,
  CURRENT_DATE + INTERVAL 1 SECOND;

Output

Snowflake

Query

SELECT
-- ** MSC-ERROR - MSCEWI1107 - INTERVAL LITERAL IS NOT SUPPORTED BY SNOWFLAKE IN THIS SCENARIO  **
--       INTERVAL 1 YEAR;

SELECT CURRENT_DATE + INTERVAL '1 year',
  CURRENT_DATE + INTERVAL '1 quarter',
  CURRENT_DATE + INTERVAL '1 month',
  CURRENT_DATE + INTERVAL '1 week',
  CURRENT_DATE + INTERVAL '1 day',
  CURRENT_DATE + INTERVAL '1 hour',
  CURRENT_DATE + INTERVAL '1 minute',
  CURRENT_DATE + INTERVAL '1 second';

Output

Snowflake does not support the scenario where the Interval data type is queried directly, on the contrary when it is used as an operator for a given date its translation is done using an Interval constant (if possible).

Interval with a DateTime part range

BigQuery

Query

SELECT INTERVAL '2-1 10' YEAR TO DAY;

SELECT CURRENT_DATE + INTERVAL '2-11' YEAR TO MONTH,
  CURRENT_DATE + INTERVAL '2-11 28' YEAR TO DAY,
  CURRENT_DATE + INTERVAL '2-11 28 16' YEAR TO HOUR,
  CURRENT_DATE + INTERVAL '2-11 28 16:15' YEAR TO MINUTE,
  CURRENT_DATE + INTERVAL '2-11 28 16:15:14' YEAR TO SECOND,
  CURRENT_DATE + INTERVAL '11 28' MONTH TO DAY,
  CURRENT_DATE + INTERVAL '11 28 16' MONTH TO HOUR,
  CURRENT_DATE + INTERVAL '11 28 16:15' MONTH TO MINUTE,
  CURRENT_DATE + INTERVAL '11 28 16:15:14' MONTH TO SECOND,
  CURRENT_DATE + INTERVAL '28 16' DAY TO HOUR,
  CURRENT_DATE + INTERVAL '28 16:15' DAY TO MINUTE,
  CURRENT_DATE + INTERVAL '28 16:15:14' DAY TO SECOND,
  CURRENT_DATE + INTERVAL '16:15' HOUR TO MINUTE,
  CURRENT_DATE + INTERVAL '16:15:14' HOUR TO SECOND,
  CURRENT_DATE + INTERVAL '15:14' MINUTE TO SECOND;

Output

Snowflake

Query

SELECT
  -- ** MSC-ERROR - MSCEWI1107 - INTERVAL LITERAL IS NOT SUPPORTED BY SNOWFLAKE IN THIS SCENARIO  **
  --       INTERVAL '2-1 10' YEAR TO DAY;

SELECT CURRENT_DATE + INTERVAL '2y, 11mm',
  CURRENT_DATE + INTERVAL '2y, 11mm, 28d',
  CURRENT_DATE + INTERVAL '2y, 11mm, 28d, 16h',
  CURRENT_DATE + INTERVAL '2y, 11mm, 28d, 16h, 15m',
  CURRENT_DATE + INTERVAL '2y, 11mm, 28d, 16h, 15m, 14s',
  CURRENT_DATE + INTERVAL '11mm, 28d',
  CURRENT_DATE + INTERVAL '11mm, 28d, 16h',
  CURRENT_DATE + INTERVAL '11mm, 28d, 16h, 15m',
  CURRENT_DATE + INTERVAL '11mm, 28d, 16h, 15m, 14s',
  CURRENT_DATE + INTERVAL '28d, 16h',
  CURRENT_DATE + INTERVAL '28d, 16h, 15m',
  CURRENT_DATE + INTERVAL '28d, 16h, 15m, 14s',
  CURRENT_DATE + INTERVAL '16h, 15m',
  CURRENT_DATE + INTERVAL '16h, 15m, 14s',
  CURRENT_DATE + INTERVAL '15m, 14s';

Output

The Interval value is transformed to a supported Snowflake format and then inserted as text inside the column. Since Snowflake does not support Interval as a data type, it is only supported in arithmetic operations. In order to use the value, it needs to be extracted and used as an Interval constant (if possible).

Interval as a Column data type

BigQuery

Query

CREATE OR REPLACE TABLE test.my_table (
  id INT NOT NULL,
  interval_column INTERVAL
);

INSERT INTO test.my_table
VALUES (1, INTERVAL '2-11 28' YEAR TO DAY);

INSERT INTO test.my_table
VALUES (2, INTERVAL '2-11 28 16:15:14' YEAR TO SECOND);

INSERT INTO test.my_table
VALUES (3, INTERVAL '11 28 16:15:14' MONTH TO SECOND);

INSERT INTO test.my_table
VALUES (4, INTERVAL '15:14' MINUTE TO SECOND);

SELECT * FROM my_table;

Output

Snowflake

Query

CREATE OR REPLACE TABLE my_table (
  id INT NOT NULL,
  interval_column VARCHAR(30) /*** MSC-WARNING - MSCEWI1036 - INTERVAL DATA TYPE CONVERTED TO VARCHAR ***/
);

INSERT INTO my_table
VALUES (1, '2y, 11mm, 28d');

INSERT INTO my_table
VALUES (1, '2y, 11mm, 28d, 16h, 15m, 14s');

INSERT INTO my_table
VALUES (1, '11mm, 28d, 16h, 15m, 14s');

INSERT INTO my_table
VALUES (1, '15m, 14s');

SELECT * FROM my_table;

Output

In BigQuery the datetime_part follows the next canonical format

[sign]Y-M [sign]D [sign]H:M:S[.F]

Interval comparison

BigQuery

Query

SELECT INTERVAL 1 YEAR = INTERVAL 1 YEAR:
SELECT CURRENT_DATE + INTERVAL '-2 -16' DAY TO HOUR =  CURRENT_DATE + INTERVAL '-2 -16' DAY TO HOUR

SELECT INTERVAL '-2 -16' DAY TO HOUR != INTERVAL '-2 16' DAY TO HOUR,
  INTERVAL '-2 -16' DAY TO HOUR <> INTERVAL '-2 16' DAY TO HOUR,
  INTERVAL '2 16:15' DAY TO MINUTE = INTERVAL '2 -16:15' DAY TO MINUTE,
  INTERVAL '2 16:15' DAY TO MINUTE > INTERVAL '2 -16:15' DAY TO MINUTE,
  INTERVAL '2 16:15' DAY TO MINUTE >= INTERVAL '2 -16:15' DAY TO MINUTE,
  INTERVAL '2 16:15' DAY TO MINUTE < INTERVAL '2 -16:15' DAY TO MINUTE,
  INTERVAL '2 16:15' DAY TO MINUTE <= INTERVAL '2 -16:15' DAY TO MINUTE,
  INTERVAL '1-5' YEAR TO MONTH = INTERVAL '1-5' YEAR TO MONTH,
  INTERVAL '1-5' YEAR TO MONTH > INTERVAL '2 16' DAY TO HOUR,
  INTERVAL '2-11 28 16:15:14.222' YEAR TO SECOND = INTERVAL '2-11 28 16:15:14.222' YEAR TO SECOND,
  INTERVAL '1-1 3' YEAR TO DAY = INTERVAL '13 3' MONTH TO DAY,
  INTERVAL '1-5' YEAR TO MONTH > INTERVAL '2 16' DAY TO HOUR;

Output

Snowflake

Query

SELECT
  '1 year' = '1 year';
SELECT CURRENT_DATE + INTERVAL '-2d, -16h' =  CURRENT_DATE + INTERVAL '-2d, -16h';

SELECT
  CURRENT_TIMESTAMP + INTERVAL '-2d, -16h' != CURRENT_TIMESTAMP + INTERVAL '-2d, 16h',
  CURRENT_TIMESTAMP + INTERVAL '-2d, -16h' <> CURRENT_TIMESTAMP + INTERVAL '-2d, 16h',
  CURRENT_TIMESTAMP + INTERVAL '2d, 16h, 15m' = CURRENT_TIMESTAMP + INTERVAL '2d, -16h, -15m',
  CURRENT_TIMESTAMP + INTERVAL '2d, 16h, 15m' > CURRENT_TIMESTAMP + INTERVAL '2d, -16h, -15m',
  CURRENT_TIMESTAMP + INTERVAL '2d, 16h, 15m' >= CURRENT_TIMESTAMP + INTERVAL '2d, -16h, -15m',
  CURRENT_TIMESTAMP + INTERVAL '2d, 16h, 15m' < CURRENT_TIMESTAMP + INTERVAL '2d, -16h, -15m',
  CURRENT_TIMESTAMP + INTERVAL '2d, 16h, 15m' <= CURRENT_TIMESTAMP + INTERVAL '2d, -16h, -15m',
  CURRENT_TIMESTAMP + INTERVAL '1y, 5mm' = CURRENT_TIMESTAMP + INTERVAL '1y, 5mm',
  CURRENT_TIMESTAMP + INTERVAL '1y, 5mm' > CURRENT_TIMESTAMP + INTERVAL '2d, 16h',
  CURRENT_TIMESTAMP + INTERVAL '2y, 11mm, 28d, 16h, 15m, 14s, 222ms' = CURRENT_TIMESTAMP + INTERVAL '2y, 11mm, 28d, 16h, 15m, 14s, 222ms',
  CURRENT_TIMESTAMP + INTERVAL '1y, 1mm, 3d' = CURRENT_TIMESTAMP + INTERVAL '13mm, 3d',
  CURRENT_TIMESTAMP + INTERVAL '1y, 5mm' > CURRENT_TIMESTAMP + INTERVAL '2d, 16h';

Output

As is known, Snowflake only supports Interval as a data type in arithmetic operations, which is why the CURRENT_TIMESTAMP function is added to each operand to correctly support the comparison.

Known Issues

1. Only arithmetic operations are supported

Snowflake Intervals have several limitations. Only arithmetic operations between DATE or TIMESTAMP and Interval Constants are supported, every other scenario is not supported. For more information please review MSCEWI1107.

2. Working with signs in the Interval data type

In BigQuery, when the substring corresponding to the year-month is preceded by a sign (+ -), it affects both the year and the month. In a similar way, it works for the substring corresponding to the time, in this case, the following affects the hour, minute, and second. An example of this is shown below.

BigQuery

SELECT CURRENT_DATE + INTERVAL '-2-11 -28 -16:15:14.222' YEAR TO SECOND;

Snowflake

Snowflake

Related EWIs

1. MSCEWI1036: Data type converted to another data type.

2. MSCEWI1107: Interval Literal Not Supported In Current Scenario.