Snowflake SQL Patterns

TIMESTAMP_NTZ, LTZ, and TZ

The three Snowflake timestamp types, what TIMESTAMP alone means, and the DST offset trap on TIMESTAMP_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

Verification

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