INTERVAL DAY TO SECOND Data Type

Description

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. (Oracle SQL Language Reference INTERVAL DAY TO SECOND Data Type)

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

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

Sample Source Patterns

Interval Day to Second in Create Table

Oracle

Query

CREATE TABLE interval_day_to_second_table
(
	interval_day_col1 interval day to second,
	interval_day_col2 interval day(1) to second(4)
);

INSERT INTO interval_day_to_second_table(interval_day_col1) VALUES ( INTERVAL '1 2:3:4.56' DAY TO SECOND ); 
INSERT INTO interval_day_to_second_table(interval_day_col2) VALUES ( INTERVAL '1 2:3:4.56' DAY(1) TO SECOND(4) );

Snowflake

Query

CREATE OR REPLACE TABLE PUBLIC.interval_day_to_second_table (
interval_day_col1 VARCHAR(20) /*** MSC-WARNING - MSCEWI1036 - INTERVAL day to second DATA TYPE CONVERTED TO VARCHAR ***/,
interval_day_col2 VARCHAR(20) /*** MSC-WARNING - MSCEWI1036 - INTERVAL day(1) to second(4) DATA TYPE CONVERTED TO VARCHAR ***/);

INSERT INTO PUBLIC.interval_day_to_second_table(interval_day_col1) VALUES ('1d, 2h, 3m, 4s, 56ms' );

INSERT INTO PUBLIC.interval_day_to_second_table(interval_day_col2) VALUES ('1d, 2h, 3m, 4s, 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: '1d, 2h, 3m, 4s, 567ms'

Value as Snowflake Interval constant: INTERVAL '1d, 2h, 3m, 4s, 567ms'

Retrieving data from an Interval Day to Second column

Oracle

Query

SELECT * FROM interval_day_to_second_table;

Result

|INTERVAL_DAY_COL1|INTERVAL_DAY_COL2|
|-----------------|-----------------|
|1 2:3:4.567      |                 |
|                 |1 2:3:4.567      |

Snowflake

Query

SELECT * FROM PUBLIC.interval_day_to_second_table;

Result

INTERVAL_DAY_COL1   |INTERVAL_DAY_COL2   |
--------------------+--------------------+
1d, 2h, 3m, 4s, 56ms|                    |
                    |1d, 2h, 3m, 4s, 56ms|

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 MSCEWI3095 and MSCEWI3096.

Related EWIs

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

2. MSCEWI3095: Operation Between Interval and Date Type not Supported.

3. MSCEWI3096: Interval Literal Not Supported In Current Scenario.