Interval

Interval data type

The Interval type is transformed to Varchar(21) because there is no direct equivalence in Snowflake

Grammar Syntax

INTERVAL <interval qualifier>

<interval qualifier>:
	<startField> TO <endField> 
	| <singleDateTimeField>

<startField>:
	<nonSecondPrimaryDateTimeField> [ (precision) ]

<endField>:
	<nonSecondPrimaryDateTimeField> 
	| SECOND [ (precision) ]

<singleDateTimeField>:
	<nonSecondPrimaryDateTimeField> [ (precision) ] 
	| SECOND [ (precision [, precision2] ) ] 

<nonSecondPrimaryDateTimeField>:
	YEAR
	| MONTH
	| DAY
	| HOUR
	| MINUTE

Uses of the Interval type columns are going to be punished in the conversion rate, but we plan to deliver a better translation for the interval type.

Sample Source Patterns

ANSI SQL Input Code

CREATE TABLE TABLE_INTERVAL (
   INTERVAL_COLUMN1 INTERVAL YEAR(10) TO SECOND,
   INTERVAL_COLUMN2 INTERVAL MONTH TO SECOND,
   INTERVAL_COLUMN3 INTERVAL MINUTE(10),
   INTERVAL_COLUMN4 INTERVAL SECOND(10)
);

SELECT INTERVAL_COLUMN1 
FROM TABLE_INTERVAL;

SELECT TABLE_INTERVAL.INTERVAL_COLUMN1 
FROM TABLE_INTERVAL;

SELECT INTERVAL_COLUMN1 AS COLUMNALIAS 
FROM TABLE_INTERVAL;

SELECT CAST(ANYCOLUMN AS INTERVAL HOUR(2) TO SECOND)
FROM TABLE_INTERVAL;

SELECT
   CASE WHEN GMT_OFFSET_VAL=-7
   THEN INTERVAL_COLUMN1
   ELSE INTERVAL_COLUMN2
   END
   FROM TABLE_INTERVAL;

Snowflake Output Code

CREATE TABLE PUBLIC.TABLE_INTERVAL (
   INTERVAL_COLUMN1 VARCHAR(21),
   INTERVAL_COLUMN2 VARCHAR(21),
   INTERVAL_COLUMN3 VARCHAR(21),
   INTERVAL_COLUMN4 VARCHAR(21)
);

SELECT INTERVAL_COLUMN1 /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR INTERVAL TYPE COLUMN IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
FROM
   PUBLIC.TABLE_INTERVAL;

SELECT TABLE_INTERVAL.INTERVAL_COLUMN1 /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR INTERVAL TYPE COLUMN IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
FROM
   PUBLIC.TABLE_INTERVAL;

SELECT INTERVAL_COLUMN1 /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR INTERVAL TYPE COLUMN IS PLANNED TO BE DELIVERED IN THE FUTURE ***/ AS COLUMNALIAS
FROM
   PUBLIC.TABLE_INTERVAL;

SELECT CAST(ANYCOLUMN AS INTERVAL HOUR(2) TO SECOND) /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR INTERVAL CAST IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
FROM
   PUBLIC.TABLE_INTERVAL;

SELECT
   CASE WHEN GMT_OFFSET_VAL=-7
   THEN INTERVAL_COLUMN1 /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR INTERVAL TYPE COLUMN IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
   ELSE INTERVAL_COLUMN2 /*** MSC-ERROR - MSCEWI1037 - TRANSLATION FOR INTERVAL TYPE COLUMN IS PLANNED TO BE DELIVERED IN THE FUTURE ***/
   END
   FROM
   PUBLIC.TABLE_INTERVAL;

Related EWIs

1. MSCEWI1037: Translation planned for the future.