How to retain a SQL DateTimeOffset in Snowflake without converting to UTC

Question

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?

Answer 1

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