1

In SQL Server I store a datetime as a DateTimeOffset data type so the table values contain the UTC offset. For Example:

2020-05-24 14:20:18.0000000 -05:00

However, once I import into Snowflake (via Fivetran) the value is converted to UTC. For Example:

2020-05-24 19:20:18.000 +0000

How can I maintain the source value after importing into Snowflake without converting to UTC?

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
user9398163
  • 13
  • 6
  • Hi - what is the data type of the target column? – NickW Dec 01 '20 at 20:40
  • TIMESTAMP_TZ so I would have expected it to be preserved but it was not. – user9398163 Dec 01 '20 at 21:48
  • I would look at what Fivetran is doing with the data. Is it recognising that the source data has a timezone offset? Are you doing any transformation to the data? – NickW Dec 01 '20 at 22:51
  • what is the default timezone on your account, user, and session? Is it possible that your account is displaying using UTC, because that's the default? – Mike Walton Dec 02 '20 at 03:26
  • Not doing any transformations on the data. I'm checking with Fivetran and will let you know. Thank you. – user9398163 Dec 02 '20 at 23:11
  • If both the source and target columns are data types that support timezone offsets then it sounds like the issue is actually in the way Fivetran is handling the data. Some ETL products will convert everything to UTC internally so a standard timestamp/datetime data construct can be used. – Nathan Griffiths Dec 03 '20 at 03:20
  • It was indeed Fivetran: According to our documentation here, specifically: Fivetran initially converts source timestamps to UTC and then loads them into your destination as UTC. the conversion you are seeing is expected behavior. You could then convert the value to your desired time zone for analysis purposes. – user9398163 Dec 04 '20 at 20:20
  • Thank you for your help! – user9398163 Dec 04 '20 at 20:20

1 Answers1

0

Notice that Snowflake has 3 different timezone types, including TIMESTAMP_LTZ and TIMESTAMP_TZ.

  • TIMESTAMP_LTZ stores UTC time with a specified precision
  • TIMESTAMP_TZ stores UTC time together with an associated time zone offset

Use TIMESTAMP_TZ to store the associated time zone offset.

https://docs.snowflake.com/en/sql-reference/data-types-datetime.html#timestamp-ltz-timestamp-ntz-timestamp-tz

Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • The column created upon initial import was TIMESTAMP_TZ but the values have been converted. – user9398163 Dec 01 '20 at 21:49
  • Maybe the info was lost in translation? What happens if you ingest the same data without Fivetran? If this works, there might be a setting on Fivetran – Felipe Hoffa Dec 01 '20 at 22:23
  • I'm checking with Fivetran and will let you know. Thank you. – user9398163 Dec 02 '20 at 23:10
  • It was indeed Fivetran: According to our documentation here, specifically: Fivetran initially converts source timestamps to UTC and then loads them into your destination as UTC. the conversion you are seeing is expected behavior. You could then convert the value to your desired time zone for analysis purposes. – user9398163 Dec 04 '20 at 20:20
  • Thank you for your help! – user9398163 Dec 04 '20 at 20:20
  • Glad to know! Please accept the answer if it was useful to you :) – Felipe Hoffa Dec 04 '20 at 20:29