timestamptz Pitfalls: The Easy Mistakes in PostgreSQL Time Handling

Infrastructure PostgreSQL Database
4 min read

The conclusion of basics chapter 2 was “points in time are timestamptz.” That conclusion stands — but there’s a separate set of traps you step on after adopting timestamptz. This article is that collection. Grab one shared principle first and they all unravel: timestamptz does not store a time zone. It stores an absolute instant converted to UTC, and merely converts through the session’s TimeZone setting for display.

Trap 1: most “why is this off by 9 hours” cases are session settings #

You query the same row and one environment shows 18:00+09 while another shows 09:00+00. The stored value isn’t different — the session TimeZone is (they’re two notations of the same absolute instant). Your database client, application driver, and server can each carry a different TimeZone, producing the “values look different per environment” effect. The standard practice: unify storage and transport layers on UTC, and convert to local time zones only in the presentation layer facing humans. Pinning application connections’ TimeZone to UTC makes this whole family of confusion disappear.

Trap 2: AT TIME ZONE behaves oppositely depending on the type #

This operator is the largest single supplier of time-handling bugs. Its meaning flips with the input type.

The dual behavior of AT TIME ZONE 
-- applied to timestamptz: "show this absolute instant as that zone's wall clock" → returns timestamp
SELECT now() AT TIME ZONE 'Asia/Seoul';          -- 2026-09-06 18:00:00 (no time zone)

-- applied to timestamp: "interpret this wall-clock figure as that zone's" → returns timestamptz
SELECT TIMESTAMP '2026-09-06 18:00' AT TIME ZONE 'Asia/Seoul';  -- 2026-09-06 09:00:00+00

The same syntax converts-for-display on one side and interprets-into-an-instant on the other. It’s safest memorized as a working rule: timestamptz → AT TIME ZONE → wall clock (timestamp); wall clock (timestamp) → AT TIME ZONE → absolute instant (timestamptz). Applying it twice round-trips back to the original type. And prefer region names like 'Asia/Seoul' over abbreviations like 'KST' — abbreviations can be ambiguous (several zones share one) or fail to track daylight saving.

Trap 3: whose midnight is “daily aggregation” cut at? #

Midnight anchors for daily aggregation 
-- cut into "days" by the session TimeZone: results can vary by environment
SELECT date_trunc('day', created_at), count(*) FROM orders GROUP BY 1;

-- cut explicitly into "days as seen from Seoul"
SELECT date_trunc('day', created_at, 'Asia/Seoul'), count(*) FROM orders GROUP BY 1;

Daily revenue and similar aggregates change with the boundary of a “day” — which zone’s midnight you cut at. Apply date_trunc or a ::date cast to a timestamptz naively and it cuts by the session TimeZone, producing the incident where report numbers shift with connection settings. The standard is naming the aggregation’s reference zone in the query (date_trunc('day', col, 'Asia/Seoul')). Incidentally, if queries filtering or grouping on such transformed values are frequent, that’s also expression-index review territory.

Trap 4: future appointments need more than timestamptz #

For records of past events, timestamptz is the right answer — but future appointments are a different problem. Store “March 10, 2027, 10:00 AM meeting in Seoul” converted to UTC now, and the moment time zone rules change in between (daylight saving adopted or abolished, and so on), the stored absolute instant no longer matches the appointment. A human appointment means “10 o’clock on that region’s wall clock,” not one specific absolute instant. The calendar-domain standard is storing the wall-clock value (timestamp) plus the time zone name (text, ‘Asia/Seoul’) separately, computing the absolute instant on demand with the rules current at that moment. Remember it as: past records are timestamptz; future appointments are wall clock + region name.

Trap 5: now() is the transaction start time #

now() (= CURRENT_TIMESTAMP) returns the transaction’s start time, and within one transaction it returns the same value no matter how often you call it. For most uses (created_at defaults and the like) that’s exactly the behavior you want — but if you want “the actual time of each step” inside a long batch transaction, you need clock_timestamp(), which gives the real time per call. This is the answer to the mystery of “it’s a long transaction, yet every log timestamp is identical.”

Summary #

  • One principle: timestamptz is UTC absolute-instant storage plus session-TimeZone display conversion. When things look off, suspect session settings first.
  • AT TIME ZONE flips meaning by type. Memorize the round trip — timestamptz→wall clock, timestamp→absolute instant — and use region names, not abbreviations.
  • Daily aggregation names its midnight’s reference zone in the query: date_trunc(‘day’, col, ‘Asia/Seoul’) is the standard form.
  • Past records are timestamptz; future appointments are wall clock + time zone name. Time zone rules change.
  • now() is pinned to transaction start. Per-step actual times come from clock_timestamp().
X