Snowflake does not support the period datatype, all periods are handled as varchar instead
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 period value constructor function 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:
- 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
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 the
PRECISIONDIGITSparameter, 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:
- Alter the session parameters
TIME_OUTPUT_FORMAT: The commands
ALTER SESSION SET TIMESTAMP_NTZ_OUTPUT_FORMAT = <format>and
ALTER 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.
create table vacations (
insert into vacations values ('Richard', period(date '2021-05-15', date '2021-06-15'));
select end(duration) from vacations;
CREATE TABLE PUBLIC.vacations (
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 ***/);
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 ***/
- Since the behavior of
PERIODand its related functions is emulated using varchar, we recommend reviewing the results obtained to ensure its correctness.
- For more support, you can email us at [email protected] or post a message to our forums. If you have a contract for support with Mobilize.Net, reach out to your sales engineer and they can direct your support needs.