0

We have one requirement/issue, the data is getting ingested from DB2, MSSQL with Fivetran/HVR and DBT to snowflake as a target. Some source applications has UTC timezone already, but target (snowflake) has pacific timezone due to source and target tables are not in time SYNC. Earlier we thought to change the snowflake timezone account parameter to UTC but seems there are few questions as below before the changes.

  1. If we change timezone parameter at account level to UTC, how to SYNC existing tables columns with UTC timezone.
  2. What need to be done at source end to SYNC the time as UTC and match with snowflake timezone.
  3. We can see in existing tables which are alrady ingessted has some columns names as below, do we really need to make changes, if we change timezone at snowflake ? INSERT_TIMESTAMP - Column name TIMESTAMP_NTZ(6) - Type(value), HVR_CHANGE_TIME - Column name TIMESTAMP_TZ(3) - Type(value), CHANGE_DATE - Column name TIMESTAMP_NTZ(6) - Type(value).
Vicky
  • 51
  • 6
  • When you are saying time in SYNC, do you mean the UTC time is inserted as it is but displayed with the pacific time offset (which makes it incorrect), or it is displayed correctly but with applied pacific offset, and you want to have in UTC? – aek Mar 15 '23 at 07:51
  • thanks @aek for reply, below is example.. Source (DB2 DB) timezone is UTC and HVR timezone is UTC, but data is inserting in snowflake and it's showing pacific timezone. – Vicky Mar 15 '23 at 10:47

1 Answers1

2

Almost all Date & Time Data Types from Snowflake have their values stored internally in UTC. Based on the data type it might be UTC time with a specified precision or UTC time together with an associated time zone offset.

For TIMESTAMP_NTZ columns the value is stored as is and no timezone is taken into account.

For TIMESTAMP_LTZ the value of TIMEZONE parameter will dictate how the value was stored. This can cause a difference between a UTC value on source system vs what we see in Snowflake (if the TIMEZONE at insertion was not UTC)

For TIMESTAMP_TZ if the time zone is not provided, the session time zone offset is used (depends again on TIMEZONE value), otherwise UTC time together with the associated time zone offset.

To show an example:

  • source system UTC value: 2023-03-15 00:41:48
  • Snowflake side TIMEZONE=America/Los_Angeles

An insert into a table with different timestamp columns shows:

alter session set TIMEZONE='America/Los_Angeles';
create or replace table dttest (tntz timestamp_ntz, tltz timestamp_ltz, ttz timestamp_tz);
insert into dttest values ('2023-03-15 00:41:48', '2023-03-15 00:41:48', '2023-03-15 00:41:48');
select * from dttest;

enter image description here

  • changing timezone on Snowflake to UTC

We now see

alter session set TIMEZONE='UTC';
select * from dttest;

enter image description here

At this stage the original UTC value 2023-03-15 00:41:48 from source system can be seen as 2023-03-15 07:41:48 in UTC on Snowflake.

In conclusion for TIMESTAMP_NTZ no change is needed, but for TIMESTAMP_TZ it depends if a timezone was specified or not when the value was inserted. If it wasn't then above example explains what might have happened and that means you might have a difference between source system and Snowflake. To find out just set the session timezone to UTC and compare the value for a row in Snowflake with the same row on source system, if they don't match, then you will need to convert the date from Snowflake to match source data.

Sergiu
  • 4,039
  • 1
  • 13
  • 21
  • 1
    @MattJohnson-Pint You were right, I've updated my answer. Hope it is more clear now. – Sergiu Mar 15 '23 at 16:36
  • update - we are preparing a test env as per above suggestions, possibly soon there will be more updates on it. – Vicky Mar 17 '23 at 12:19