Importing DatetimeOffset Data into Snowflake

user_12254324 0
Viewed 1240

When I load DatatimeOffset Data into Snowflake the offset defaults to the Timezone of America/Los_Angeles.

The only question I have besides this is how we maintain the provided UTC offset with our TIMESTAMP_TZ fields?

I have also tried copying Data containing a TIMEZONE_TZ into another table and the offset does not get overwritten. It only seems to happen when you import data from an external source.

Source Database

Snowflake Table Creation

Importing Data into Snowflake

Target Snowflake Database

I expect the data being imported into Snowflake to retain the existing Datatimeoffsetand not be overwritten by the Snowflake Table.

I also tried using the TIMEZONE_LTZ and had the same error.

Edited to include screenshots of the issue.

snowflake-cloud-data-platform sql
2 Answers

Please read about the differences between TIMESTAMP_LTZ , TIMESTAMP_NTZ , TIMESTAMP_TZ.

You have to use the one of the three that suits your requirements. From your example it seems you are mixing them. Of course you can have both TZ and NTZ columns, but TZ time offsets are destoyed by converting to anything else, eg. casting to TIMESTAMP during COPY into <table>.

TIMESTAMP depends on the TIMESTAMP_TYPE_MAPPING parameter and defaults to TIMESTAMP_NTZ, which has no timezone offset.

If you want to retain mixed offsets you have to use TIMESTAMP_TZ, eg:

CREATE OR REPLACE TABLE T AS
SELECT TO_TIMESTAMP_TZ('2019-10-29 02:58:23.123-05:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM') T
  UNION ALL
SELECT TO_TIMESTAMP_TZ('2019-10-28 23:58:23.123-08:00', 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM');

(Side note: don't use UNION above, the two timestamps are the same time in different zones and will become one)

So: Use TIMESTAMP_TZ if you want to retain different offsets, use TIMESTAMP_LTZ if you want to convert everything to the session TIMEZONE and use TIMESTAMP_NTZ if you don't want offsets (will possibly be displayed as UTC/Z if you use a timezone format).

user_121664970

Timezone of the timestamp data is governed by the value set to parameter TIMEZONE and its default value is 'America/Los_Angeles'. You could check the same with below query.

show parameters like '%timezone%';

To set it to UTC, run the below query.

alter account set timezone = 'UTC';

This parameter can be set at account level, session level and user level. Refer the link below for more details.

https://docs.snowflake.net/manuals/sql-reference/parameters.html#timezone

user_122565050
Related