INTERVAL
Interval data type and usages
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)
Syntax
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
Input Code:
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 Code:
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0107 - 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';
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
Input Code:
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 Code:
SELECT
!!!RESOLVE EWI!!! /*** SSC-EWI-0107 - 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';
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
Input Code:
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 test.my_table;
Output Code:
CREATE OR REPLACE TABLE test.my_table (
id INT NOT NULL,
interval_column VARCHAR(30) !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{ "origin": "sf_sc", "name": "snowconvert", "version": { "major": 0, "minor": 0, "patch": "0" }, "attributes": { "component": "bigquery", "convertedOn": "04/01/2025", "domain": "test" }}';
INSERT INTO test.my_table
VALUES (1, '2y, 11mm, 28d');
INSERT INTO test.my_table
VALUES (2, '2y, 11mm, 28d, 16h, 15m, 14s');
INSERT INTO test.my_table
VALUES (3, '11mm, 28d, 16h, 15m, 14s');
INSERT INTO test.my_table
VALUES (4, '15m, 14s');
SELECT * FROM
test.my_table;
In BigQuery the datetime_part follows the next canonical format:
[sign]Y-M [sign]D [sign]H:M:S[.F]
Interval comparison
Input Code:
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 Code:
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';
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.
Input:
SELECT CURRENT_DATE + INTERVAL '-2-11 -28 -16:15:14.222' YEAR TO SECOND;
Output:
SELECT CURRENT_DATE + INTERVAL '-2y, -11mm, -28d, -16h, -15m, -14s, -222ms';
Related EWIs
SSC-EWI-0036: Data type converted to another data type.
SSC-EWI-0107: Interval Literal Not Supported In Current Scenario.
Last updated