flashman
← All posts

PostgreSQL AT TIME ZONE direction debugging

Debug PostgreSQL AT TIME ZONE by reading the input type first. The operator yields an instant from a plain timestamp and a wall clock from timestamptz.

2026-10-15 · 6 min read · Rahul Chitturi

  • sql
  • postgresql
  • timezones

AT TIME ZONE does not mean convert everything to this zone and keep the same type. The result type is the opposite of the input type. A timestamp without time zone is read as a wall clock in the zone you name and returned as timestamptz. A timestamptz is converted to that zone's wall clock and returned as timestamp without time zone.

Applying the operator twice is a different operation from displaying the time. The second application interprets the wall clock you just produced as if it belonged to the next zone.

Match one operator to one question

On 15 October 2026, New York is on Eastern Daylight Time, four hours behind UTC. TIMESTAMP '2026-10-15 09:30:00' AT TIME ZONE 'America/New_York' means that wall clock is New York time, so the instant is 13:30 UTC. TIMESTAMPTZ '2026-10-15 13:30:00+00' AT TIME ZONE 'America/New_York' means show that instant in New York, so the wall clock is 09:30 with no zone attached.

TIMESTAMPTZ '2026-10-15 13:30:00+00' AT TIME ZONE 'UTC' AT TIME ZONE 'America/New_York' first produces the wall clock 13:30, then reads 13:30 as New York time. The instant becomes 17:30 UTC. That is a four-hour move, not a display format.

  • Stop after one conversion when you only needed the wall clock.
  • Do not compare that plain timestamp to a timestamptz column.
  • A later cast uses the session TimeZone, not the zone in the operator.
  • Print the result type with pg_typeof when the hour looks shifted.

Prefer an IANA name over an abbreviation

EST in PostgreSQL's abbreviation list is a fixed offset of five hours, not America/New_York. In October, New York is four hours behind UTC. TIMESTAMP '2026-10-15 09:30:00' AT TIME ZONE 'EST' is 14:30 UTC, one hour later than the same wall clock in America/New_York.

A timestamp without time zone literal does not keep an offset you wrote on it. The offset is discarded and the remaining clock is what AT TIME ZONE sees. An ambiguous fall-back hour has two possible offsets, so select the timestamptz and render it with an offset instead of assuming which one you got.

Check the instant outside the session

Session TimeZone changes how timestamptz is displayed. It does not change the zone argument of AT TIME ZONE. Convert the instant you stored to a Unix value and compare that number with the API payload.

Format the statement in Flashman's SQL formatter, convert the instant in the timestamp tool, and diff the JSON that left the database with the JSON the client parsed. The timestamp tool takes seconds. Divide a millisecond value by 1000 before you paste it.

Try these tools