MSCEWI2053

Snowflake does not support the period datatype, all periods are handled as varchar instead

This is a deprecated version of the SnowConvert documentation, please visit the official site .

Severity

Low

Description

Teradata has a period data type used to represent a time interval, with instances of this type having a beginning and ending bound of the same type (time, date or timestamp) along with a set of functions that allow to initialize and manipulate period data like PERIOD, BEGIN, END, OVERLAPS, etc.

Since the period type is not supported by Snowflake, SnowConvert transforms this type and its related functions using the following rules:

Any period type declaration in column tables is migrated as a varchar column
The is migrated to PERIOD_UDF, a User Defined Function that generates a string representation of the original period, concatenating the beginning bound with the ending bound using an asterisk (*) as a separator. Examples:

UDF

Result

PUBLIC.PERIOD_UDF(date '2000-05-15', date '2010-11-14')

'2000-05-15*2010-11-14'

PUBLIC.PERIOD_UDF(time '10:20:15')

'10:20:15*10:20:16'

PUBLIC.PERIOD_UDF(timestamp '2007-12-15 08:15:30', timestamp '2020-12-15 10:30:00')

'2007-12-15 08:15:30*2020-12-15 10:30:00'

Supported functions that expect period type parameters are migrated to UDFs as well, these UDFs expect the parameters to be varchar variables in the form'beginningBound*EndingBound'as shown above

Precision of generated varchar representations

PERIOD_UDF generates the varchar representation of a period using the default formats for timestamps and time specified in Snowflake, this means timestamps will have three precision digits and time variables will have zero, because of this you may find that the results have a higher/lower precision from the expected, there are two options to modify how many precision digits are included in the resulting string:

Use the three parameters version of PERIOD_UDF: This overload of the function takes thePRECISIONDIGITSparameter, an integer between 0 and 9 to control how many digits of the fractional time part will be included in the result. Note that even if Snowflake supports up to nine digits of precision the maximum in Teradata is six. Example:

Call

Result

PUBLIC.PERIOD_UDF(time '13:30:45.870556', time '15:35:20.344891', 0)

'13:30:45*15:35:20'

PUBLIC.PERIOD_UDF(time '13:30:45.870556', time '15:35:20.344891', 2)

'13:30:45.87*15:35:20.34'

PUBLIC.PERIOD_UDF(time '13:30:45.870556', time '15:35:20.344891', 5)

'13:30:45.87055*15:35:20.34489'

Alter the session parameters TIMESTAMP_NTZ_OUTPUT_FORMAT and TIME_OUTPUT_FORMAT: The commands ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = <format> andALTER SESSION SET TIME_OUTPUT_FORMAT = <format>
can be used to modify the formats Snowflake uses by default for the current session, modifying them to include the desired number of precision digits changes the result of future executions of PERIOD_UDF for the current session.

Example code

Input code:

create table vacations (
    employeeName varchar(50),
    duration period(date)
);

insert into vacations values ('Richard', period(date '2021-05-15', date '2021-06-15'));

select end(duration) from vacations;

Output code:

CREATE TABLE PUBLIC.vacations (
employeeName varchar(50),
duration VARCHAR(24) /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/
);

INSERT INTO PUBLIC.vacations VALUES ('Richard', PUBLIC.PERIOD_UDF(date '2021-05-15', date '2021-06-15') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/);

SELECT
PUBLIC.PERIOD_END_UDF(duration) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_END_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/
FROM PUBLIC.vacations;

Recommendations

Since the behavior ofPERIODand its related functions is emulated using varchar, we recommend reviewing the results obtained to ensure its correctness.

PreviousMSCEWI2052 NextMSCEWI2054

Last updated 1 year ago

create table vacations ( employeeName varchar(50), duration period(date) ); insert into vacations values ('Richard', period(date '2021-05-15', date '2021-06-15')); select end(duration) from vacations;

CREATE TABLE PUBLIC.vacations ( employeeName varchar(50), duration VARCHAR(24) /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/ ); INSERT INTO PUBLIC.vacations VALUES ('Richard', PUBLIC.PERIOD_UDF(date '2021-05-15', date '2021-06-15') /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/); SELECT PUBLIC.PERIOD_END_UDF(duration) /*** MSC-WARNING - MSCEWI1020 - CUSTOM UDF 'PERIOD_END_UDF' INSERTED. ***/ /*** MSC-WARNING - MSCEWI2053 - SNOWFLAKE DOES NOT SUPPORT THE PERIOD DATATYPE, ALL PERIODS ARE HANDLED AS VARCHAR INSTEAD ***/ FROM PUBLIC.vacations;