2026-10-15 · 8 min read
- sql
- postgresql
- timezones
PostgreSQL's AT TIME ZONE operator converts between timestamp without time zone and timestamp with time zone. The direction depends on the input. It does not stamp a zone onto a value and leave the type alone, and it does not change the session TimeZone setting.
A broader schema still needs a contract for instants, local datetimes, and dates. This operator is the conversion step inside that contract. Using it twice answers a different question from using it once.
Read the input type, then the result type
timestamp without time zone AT TIME ZONE zone treats the clock fields as local time in zone and returns timestamptz. timestamptz AT TIME ZONE zone converts that instant into zone and returns timestamp without time zone, which is the wall clock with the zone removed.
On 15 October 2026, America/New_York is Eastern Daylight Time, UTC−4. TIMESTAMP '2026-10-15 09:30:00' AT TIME ZONE 'America/New_York' is the instant 13:30 UTC. TIMESTAMPTZ '2026-10-15 13:30:00+00' AT TIME ZONE 'America/New_York' is the wall clock 09:30, typed as timestamp without time zone.
- Ask pg_typeof on the expression when a comparison looks shifted.
- Stop after one operator when the wall clock was the goal.
- Do not compare that plain timestamp with a timestamptz column.
- Remember that a later cast uses the session zone.
A second operator moves the instant
TIMESTAMPTZ '2026-10-15 13:30:00+00' AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' first yields the wall clock 13:30. The second operator reads 13:30 as New York local time and returns 17:30 UTC. The original instant moved by the offset between those zones.
That double form is sometimes what a migration wants, and it is easy to write when you meant to format a column for a report. If the report only needs the wall clock, select the first result and format it in the client. If the migration needs the shifted instant, write both zones in the comment above the statement.
Use IANA names and print ambiguous hours
Abbreviations in PostgreSQL's timezone abbreviation list are fixed offsets. EST stays five hours behind UTC in October, while America/New_York is four hours behind on 15 October 2026. The same wall clock is a different instant under those two names.
A timestamp without time zone does not store an offset written on a literal. The offset is dropped. A fall-back hour occurs twice, so select the timestamptz and display it with a numeric offset. A spring-forward hour does not occur at all. Do not assume the original clock fields survived.
Round-trip the instant in seconds
Store the instant, the zone name, and the wall clock you showed the user as three fields in the ticket. Convert the instant to Unix seconds and compare it with the API. Session TimeZone can make two clients print different clocks for one timestamptz without changing the stored instant.
Format the SQL in Flashman's SQL formatter, convert seconds in the timestamp tool, and diff the JSON on each side of the driver. The timestamp tool interprets the number as seconds. A millisecond clock from the application has to be divided by 1000 first. JSON formatting helps when the wire value is a string without an offset.