In PostgreSQL, what does a timestamptz column actually store, and how does the session TimeZone setting change what you read back?
answer
- one instant, no zone kept
- normalised to UTC on input
- converted again on output
- a per-session setting
- AT TIME ZONE flips the type
basics
~10 sA PostgreSQL timestamptz stores one UTC instant and discards the zone you wrote. Input without an offset is read in the session's TimeZone, and every value is displayed converted to that TimeZone.
solid answer
~50 sDespite its name, `timestamp with time zone` stores no zone. On input PostgreSQL converts the value to UTC — using the offset or zone name in the literal, or the session's `TimeZone` when there is none — and keeps only that 8-byte instant. On output it converts the instant back into the current `TimeZone`, so two sessions with different settings print different local text for the same row. Plain `timestamp` (without time zone) does the opposite: it keeps the wall-clock fields and silently ignores any zone written in the literal. So for when a flight actually departs, use `timestamptz`; keep the airport's IANA zone name in its own column if you need local time, and convert with `AT TIME ZONE`, which turns a `timestamptz` into a local `timestamp` and a `timestamp` back into an instant.
code
sql · 15 linesCREATE TABLE departure (
flight_no text NOT NULL,
origin_tz text NOT NULL, -- IANA name, e.g. 'Asia/Tokyo'
departs_at timestamptz NOT NULL
);
SET TimeZone = 'UTC';
INSERT INTO departure VALUES ('NH203', 'Asia/Tokyo', '2026-11-20 10:35 Asia/Tokyo');
SELECT departs_at FROM departure; -- 2026-11-20 01:35:00+00
SET TimeZone = 'America/New_York';
SELECT departs_at FROM departure; -- 2026-11-19 20:35:00-05
SELECT departs_at AT TIME ZONE origin_tz FROM departure; -- 2026-11-20 10:35:00
SELECT '2026-11-20 10:35 Asia/Tokyo'::timestamp; -- 2026-11-20 10:35:00go deeper
Recall that timestamptz is the type for instants, that it is stored as UTC, and that the session TimeZone only changes how values are read in and printed.
Explain the input rules for both types, why plain timestamp ignores a zone in its literal, and how AT TIME ZONE turns one type into the other.
Show where it breaks in production: casts that depend on a driver's TimeZone, abbreviations without DST rules, and future local-time schedules exposed to time-zone rule changes.
Set the convention for a whole system: which columns are instants, which are local civil times with a zone, and who owns updating zone data across services.
## The name misleads: no zone is stored PostgreSQL has two timestamp types. **`timestamp`** — formally `timestamp without time zone`, and what plain `timestamp` means — and **`timestamptz`**, short for `timestamp with time zone`. Both take **8 bytes**, both have **1 microsecond** resolution, and both span 4713 BC to 294276 AD. The word *with* suggests the zone is kept beside the value. It is not. A `timestamptz` holds a single **instant**, normalised to UTC; the zone that was written in the input is used once, to find that instant, and then thrown away. The manual says so directly: the originally stated or assumed time zone is not retained. ## Input: how a literal becomes an instant 1. If the literal carries an offset or a zone — `'2026-11-20 10:35+09'`, `'2026-11-20 10:35 Asia/Tokyo'` — PostgreSQL uses it to compute UTC. 2. If it carries none — `'2026-11-20 10:35'` — the value is read as local time in the session's **`TimeZone`** setting. 3. The result is stored as UTC. For plain `timestamp` the rule is reversed: PostgreSQL **silently ignores** any zone indication in the literal and stores the date and time fields as written. `'2026-11-20 10:35 Asia/Tokyo'::timestamp` is simply `2026-11-20 10:35:00`. ## Output: the session decides the rendering When a `timestamptz` is printed, it is converted from UTC to the session's `TimeZone` and shown with that offset. Key facts about the setting: - `TimeZone` is an ordinary user-settable parameter: `SET TimeZone = 'Asia/Tokyo'` changes it for the session, and drivers often set it on connect; - the built-in default is `GMT`, but `initdb` writes the host's zone into `postgresql.conf`, so servers commonly run with whatever the machine had; - changing it affects how new literals are read and how values are printed, but **no stored value moves**; - use full IANA names such as `America/New_York`, listed in the `pg_timezone_names` view. An abbreviation such as `EST` is a fixed offset with no daylight-saving rules, and `TimeZone` cannot be set to one. ## timestamp vs timestamptz | | `timestamp` | `timestamptz` | |---|---|---| | What it holds | wall-clock date and time | an instant, normalised to UTC | | Zone in the input | silently ignored | used to compute UTC | | Effect of `TimeZone` | none on storage or output | read on input, applied on output | | Storage | 8 bytes | 8 bytes | | Fits | a local schedule time with its zone kept elsewhere | when something happened or will happen | ## Converting with AT TIME ZONE and AT LOCAL `AT TIME ZONE` changes the type as well as the value: - `timestamptz AT TIME ZONE 'Asia/Tokyo'` returns a **`timestamp`**: the wall-clock time an observer in Tokyo would read; - `timestamp AT TIME ZONE 'Europe/Paris'` returns a **`timestamptz`**: the instant at which Paris clocks showed that time; - the zone may come from a column, so `departs_at AT TIME ZONE origin_tz` renders each departure in its own airport's local time; - since PostgreSQL 17, `AT LOCAL` is shorthand for converting with the session's own `TimeZone`. Casting between the two types without `AT TIME ZONE` silently uses the session's `TimeZone`, which is where application bugs hide: the same cast gives different instants in differently configured sessions. ## Where the session zone leaks into results The stored instant never changes, but several everyday operations on a `timestamptz` are computed **in the session's `TimeZone`**, so the same query can return different rows from different clients: - `departs_at::date` takes the calendar date as seen in the session zone, so a 01:35 UTC departure belongs to different days in Tokyo and New York; - `date_trunc('day', departs_at)` truncates to midnight in the session zone unless you pass the zone as a third argument, as in `date_trunc('day', departs_at, 'Asia/Tokyo')`; - a filter such as `departs_at >= '2026-11-20'` reads the bare literal as midnight in the session zone; - reports grouped by day therefore move rows between days when a reporting tool connects with a different setting. The fix is to say which zone a business day belongs to — usually the airport's — and write it into the query rather than inheriting it from the connection. ## Modelling departures For a flight-schedule service the usual shape is: 1. `departs_at timestamptz` — the instant the aircraft leaves, comparable across every airport; 2. `origin_tz text` — the departure airport's IANA zone name, for display; 3. queries that compare or sort on `departs_at`, and a projection `departs_at AT TIME ZONE origin_tz` for the boarding pass. There is one exception worth knowing. A departure scheduled far ahead is really a promise about **local** wall time; if the airport's government changes its daylight-saving rules before then, the stored UTC instant no longer matches the local time printed on tickets. For those, keep the local `timestamp` plus the zone name and compute the instant when it is needed.
- Why is a timestamptz risky for a departure scheduled a year ahead in local time?The UTC instant is computed with today's time-zone rules. PostgreSQL ships its own copy of the IANA zone data and updates it in minor releases (or uses the OS copy when built with `--with-system-tzdata`). If the country changes its daylight-saving rules first, the stored instant renders at a different local time than the published 10:35. Store the local `timestamp` plus the zone name and derive the instant when needed.
- In PostgreSQL, why use 'America/New_York' rather than 'EST' in a literal or the TimeZone setting?An abbreviation is a fixed UTC offset: `EST` means UTC-5 even in July, when New York is actually on UTC-4. A full IANA name carries the daylight-saving rules, so the offset is right for each date. `TimeZone` and `log_timezone` cannot be set to an abbreviation at all; abbreviations are accepted only in input values and `AT TIME ZONE`.
- timestamptz remembers the zone each value was inserted with.
- Changing TimeZone shifts or rewrites the stored timestamptz values.
- timestamptz needs more storage than timestamp to hold the offset.
- Plain timestamp converts a literal like 10:35+09 to UTC.
- Storing timestamp and assuming the server's zone is the safe default.
Assumes PostgreSQL 18; the AT LOCAL shorthand mentioned here arrived in 17, and older releases need AT TIME ZONE with the session's zone written out.