TIMESTAMP_NTZ, LTZ, and TZ
The problem
A table has a column declared as TIMESTAMP. In a session in America/New_York it looks right. In a session in UTC, or six months later after a DATEADD, it does not. Official Snowflake documentation defines three timestamp types, and says the bare name TIMESTAMP is a synonym controlled by the session parameter TIMESTAMP_TYPE_MAPPING. The default mapping is TIMESTAMP_NTZ.
A working example
From official date and time documentation (examples and rules not re-executed on Snowflake):
| Type | What official docs say it stores | How operations run |
|---|---|---|
TIMESTAMP_LTZ |
UTC values with a precision | In the current session time zone (TIMEZONE parameter). Synonyms include TIMESTAMPLTZ. |
TIMESTAMP_NTZ |
“Wallclock” time, no time zone | Without taking any time zone into account. Default for TIMESTAMP. DATETIME is synonymous with TIMESTAMP_NTZ. |
TIMESTAMP_TZ |
UTC values plus an offset | With the offset specific to each record. If no zone is provided at write time, the session offset is used. |
-- Snowflake syntax from official documentation (not executed)
-- Do this: be explicit.
CREATE TABLE events (
event_id INTEGER,
occurred_at TIMESTAMP_LTZ, -- instant in time (logs, audits, 'when did this happen?')
local_slot TIMESTAMP_NTZ, -- '9:00 on a calendar poster', no zone
scheduled_at TIMESTAMP_TZ -- instant plus a stored offset
);
-- Avoid this if you care what the column actually is:
CREATE TABLE events_ambiguous (
occurred_at TIMESTAMP -- becomes TIMESTAMP_NTZ unless TIMESTAMP_TYPE_MAPPING is changed
);
ALTER SESSION SET TIMESTAMP_TYPE_MAPPING = TIMESTAMP_NTZ;
Official comparison rule for TIMESTAMP_TZ: values are compared in UTC. Their documented example is that 2024-01-01 00:00:00 +0000 equals 2024-01-01 01:00:00 +0100.
Official docs also recommend using years between 1582 and 9999 for DATE and TIMESTAMP values, because of Gregorian calendar limits.
The gotcha
TIMESTAMP_TZ stores an offset, not a named time zone. Official docs are blunt: converting a value to TIMESTAMP_TZ in January in America/Los_Angeles stores -0800. If you then DATEADD(MONTH, 6, …), the offset stays -0800, even though Los Angeles is -0700 in July. Their documented DATEADD example is there to show this. If you need DST-aware calendar arithmetic, do not assume TIMESTAMP_TZ is a time-zone database.
TIMESTAMP_LTZ does follow the session time zone for operations and display. That is convenient in one session and confusing when two sessions with different TIMEZONE settings look at the same column and disagree about the clock face. The underlying UTC instant is what is stored.
AT (TIMESTAMP => '2026-04-25 09:00:00') without a cast is treated as UTC / TIMESTAMP_NTZ in official clone documentation. See Time Travel and clones.
When not to use this
- Do not use bare
TIMESTAMPin a shared schema. SpellTIMESTAMP_NTZ,TIMESTAMP_LTZ, orTIMESTAMP_TZ. - Do not use
TIMESTAMP_TZif what you wanted was “always display and compute in a named zone with DST.” Read the official offset limitation first. - Do not use
TIMESTAMP_NTZfor audit “this happened at an instant in the real world” columns — two sessions will not be able to recover a zone, because none was stored. - For a date-only business day,
DATEis enough.
Type descriptions, default TIMESTAMP_TYPE_MAPPING, the DATETIME synonym, UTC comparison of TIMESTAMP_TZ, and the documented DST / offset retention behavior after DATEADD are from official Snowflake date and time documentation. They were not executed on Snowflake.
Official docs
- Date and time data types
- CREATE … CLONE (timestamp casting in
AT)