INTERVAL DAY TO SECOND Data Type
Description
INTERVAL DAY TO SECOND specify an interval literal to define a duration in days, hours, minutes, and seconds. (RedShift SQL Language Reference Interval data type)
There is no equivalent for this data type in Snowflake, it is currently transformed to VARCHAR
.
Grammar Syntax
INTERVAL day_to_second_qualifier [ (fractional_precision) ]
day_to_second_qualifier:
{ DAY | HOUR | MINUTE | SECOND | DAY TO HOUR | DAY TO MINUTE | DAY TO SECOND |
HOUR TO MINUTE | HOUR TO SECOND | MINUTE TO SECOND }
The use of the Interval data type is planned for implementation in future updates.
Sample Source Patterns
Interval Day to Second in Create Table
Input
CREATE TABLE interval_day_to_second_table
(
interval_day_col1 INTERVAL DAY TO HOUR,
interval_day_col2 INTERVAL DAY TO SECOND(4)
);
INSERT INTO interval_day_to_second_table(interval_day_col1) VALUES ( INTERVAL '1 2' DAY TO HOUR );
INSERT INTO interval_day_to_second_table(interval_day_col2) VALUES ( INTERVAL '1 2:3:4.56' DAY TO SECOND(4));
Output
CREATE TABLE interval_day_to_second_table
(
interval_day_col1 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DAY TO HOUR DATA TYPE CONVERTED TO VARCHAR ***/!!!,
interval_day_col2 VARCHAR !!!RESOLVE EWI!!! /*** SSC-EWI-0036 - INTERVAL DAY TO SECOND(4) DATA TYPE CONVERTED TO VARCHAR ***/!!!
)
COMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"redshift"}}'
;
INSERT INTO interval_day_to_second_table(interval_day_col1) VALUES ('1days, 2hours');
INSERT INTO interval_day_to_second_table(interval_day_col2) VALUES ('1days, 2hours, 3mins, 4secs, 56ms');
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).
Original Oracle value: INTERVAL '1 2:3:4.567' DAY TO SECOND
Value stored in Snowflake column: '1days, 2hours, 3mins, 4secs, 56ms'
Value as Snowflake Interval constant: INTERVAL '1days, 2hours, 3mins, 4secs, 56ms'
Retrieving data from an Interval Day to Second column
Input
SELECT * FROM interval_day_to_second_table;
Output
SELECT * FROM
interval_day_to_second_table;
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.
Related EWIs
SSC-EWI-0036: Data type converted to another data type.
Last updated