TIMESTAMPS in Snowflake? NTZ vs LTZ vs TZ

There are 3 different timestamp types in Snowflake:

• TIMESTAMP_NTZ
• TIMESTAMP_TZ
• TIMESTAMP_LTZ

To Here is an example of changing a TIMEZONE at the session level:

ALTER SESSION SET TIMEZONE=’Australia/Sydney’;

If you now run this command:

SHOW PARAMETERS LIKE ‘%TIMEZONE%’;

The output shows the current value is set at the session level, and it’s different from the default value:

Fig.1

In the following article, we will discuss different timestamp types available in Snowflake and use cases for each:

TIMESTAMP_NTZ (Timestamp without Timezone):

TIMESTAMP_NTZ is the datatype for timestamps without a timezone (ntz = no time zone). This is also referred to as “walltime” as it is the time you would get by looking at a random clock on the wall and writing it down.

ALTER SESSION SET TIMEZONE = 'America/New_York';
CREATE OR REPLACE TABLE TS_TEST(ID NUMERIC,TS TIMESTAMP_NTZ);
INSERT INTO TS_TEST VALUES(1,CURRENT_TIMESTAMP);
ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
INSERT INTO TS_TEST VALUES(2,CURRENT_TIMESTAMP);
SELECT * FROM TS_TEST;

Fig.2

In Fig. 2 timestamp values are getting stored without an UTC offset associated to it.

TIMESTAMP_TZ (Timestamp with Timezone):

It is the datatype for timestamps with timezones (tz = time zone). Annoyingly, Snowflake does not store the timezone associated with this timestamp. Instead, it stores the UTC offset associated with the timezone associated with this timestamp.

ALTER SESSION SET TIMEZONE = 'America/New_York';
CREATE OR REPLACE TABLE TS_TEST(ID NUMERIC,TS TIMESTAMP_TZ);
INSERT INTO TS_TEST VALUES(1,CURRENT_TIMESTAMP);
ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
INSERT INTO TS_TEST VALUES(2,CURRENT_TIMESTAMP);
SELECT * FROM TS_TEST;

Fig. 3

In Fig. 3 timestamp values are getting stored with an UTC offset associated to it.

TIMESTAMP_LTZ (Timestamp with Local Timezone):

Snowflake uses TIMESTAMP_LTZ for timestamps with timezones in the current session timezone (LTZ = local time zone). Ideally, this would make consumers automatically work with timestamps in their own local timezone . SQL code interacting with TIMESTAMP_LTZ datatypes will not be deterministic.

ALTER SESSION SET TIMEZONE = 'America/New_York';
CREATE OR REPLACE TABLE TS_TEST(ID NUMERIC,TS TIMESTAMP_LTZ);
INSERT INTO TS_TEST VALUES(1,CURRENT_TIMESTAMP);

Fig.4

ALTER SESSION SET TIMEZONE = 'America/Los_Angeles';
INSERT INTO TS_TEST VALUES(2,CURRENT_TIMESTAMP);
SELECT * FROM TS_TEST;

Fig. 5

There was a difference between Fig.4 and Fig.5. When the session time zone is changed, it results in a change of time for ID -> 1 with offset in Fig.5.

References:-

• https://www.snowflake.com/

About Me :

I am Data Engineer and Cloud Architect! and am currently working as a Senior Consultant in EY GDS. I have worked on many legacy data warehouses, big data implementations, cloud platforms and migrations. You can reach out to me in LinkedIn if you need any further help on certification, Data Solutions and Implementations!