One November night, our usage-billing rollup charged forty-one customers for twenty-five hours of a twenty-four-hour day. The pipeline grouped events by day, and the day in question — the end of daylight saving time in the customer's zone — legitimately contained twenty-five hours, because the rollup ran date_trunc('day', created_at) against sessions whose TimeZone was America/New_York, and the 01:00 hour occurred twice. The spring version of the same bug had run eight months earlier and nobody noticed, because a twenty-three-hour day just looks like slightly low usage. These bugs are brutal for a specific reason: they fire twice a year, at night, in code that passes every test, because tests rarely simulate a session time zone, let alone a DST transition.
We have since made time zones a checklist item in every query review that touches a calendar boundary. This is the mental model that ended the incidents: what timestamptz actually stores, where the session zone sneaks into results, and the three rules that keep time boring.
How does PostgreSQL actually store a timestamptz?
It stores microseconds since 2000-01-01 00:00:00 UTC — an absolute instant, with no time zone information stored anywhere in the value. The TimeZone setting is not metadata on the data; it is a display and parsing convention applied at the session level, which means the same stored instant renders as 2026-11-01 01:30:00-04 and 2026-11-01 01:30:00-05 to two sessions an hour apart, and both renderings are honest. timestamp without time zone, by contrast, stores a raw wall-clock reading with no anchor to any instant — it is the type that looks simpler and causes the quiet corruption, because a wall clock reading without a zone is a fact about nothing. Every operational rule in this article follows from that one storage fact: the database never stores "when it was in New York," it stores an instant, and every zone-sensitive decision happens at query time, under whatever TimeZone the session happens to hold.
Why do daily aggregates break on DST transition days?
Because calendar functions like date_trunc and date bucket boundaries are evaluated in the session's time zone, and days in a real zone are not all twenty-four hours long. date_trunc('day', created_at) asks "which calendar day does this instant fall in, in my session's zone?" — so a session in UTC and a session in America/New_York put the same row in different buckets, and in a DST zone one day a year has twenty-five hours and one has twenty-three. Our billing bug was exactly this: the daily cap logic assumed 86,400 seconds per bucket, and the bucket politely contained 90,000. The companion trap is interval arithmetic, where '1 day' and '24 hours' are deliberately different things: adding interval '1 day' advances the wall clock by one calendar day — twenty-three or twenty-five real hours across a transition — while interval '24 hours' advances the instant by exactly 86,400 seconds:
SET TIME ZONE 'America/New_York';
SELECT ts,
ts + interval '1 day' AS plus_cal_day,
ts + interval '24 hours' AS plus_24h
FROM (VALUES ('2026-11-01 00:00:00-04'::timestamptz)) AS v(ts);
-- plus_cal_day renders 2026-11-02 00:00:00-05 (25 real hours later)
-- plus_24h renders 2026-11-01 23:00:00-05 (exactly 86400 seconds)Neither is a bug — PostgreSQL documents the distinction — but a billing window written with one and reasoned about with the other is an incident. The fix for calendar buckets that must be stable is to name the zone in the query rather than inherit it from the session: bucket on (created_at AT TIME ZONE 'America/New_York') so the result is identical no matter who runs it, and audit the rollup queries that feed money or capacity math first. The aggregation patterns this pairs with are covered in the incremental aggregation notes.
What does AT TIME ZONE actually do to a value?
It flips the type, and the direction of the flip depends on what you feed it — timestamptz AT TIME ZONE 'zone' returns timestamp (the wall-clock reading of that instant in that zone), while timestamp AT TIME ZONE 'zone' returns timestamptz (the instant that wall-clock reading refers to). Chained twice, you get your original value back; applied once to the wrong type, you silently reinterpret data. The production failure mode is the single application to a timestamptz stored in a reporting column: the value stops being an instant and becomes a wall clock, every downstream comparison against now() quietly compares unlike types, and PostgreSQL's implicit casts paper over it until a boundary case exposes the lie. My rule in review: any AT TIME ZONE in a merge request must be accompanied by a comment naming the input type and the intended output type, because the operator reads identically in both directions and the wrong direction compiles fine. And always name the zone in full — 'America/New_York', not 'EST' — because abbreviations are ambiguous worldwide (IST means three different zones on three continents) and, more subtly, an abbreviation like EST hard-codes a fixed offset while the named zone carries the DST rules that produced this article.
Where does a session's time zone come from, and why do poolers make it worse?
From the TimeZone GUC, which starts from the server's setting but is freely overridden per session — libpq clients inherit PGTZ or the host zone, drivers set their own, and any connection can SET TIME ZONE at will. That last fact is what makes connection pooling dangerous: in transaction-mode pooling, a SET TIME ZONE issued by one request vanishes at the next transaction boundary, so request two inherits the server default while request one's author believes the zone is pinned — the full taxonomy of session-state surprises like this is in the PgBouncer transaction mode notes. Our rule, enforced at the driver level: the application sets its zone explicitly at connect time — options='-c TimeZone=UTC' or the driver equivalent — and never relies on SET inside a transaction. Two related settings keep their own surprises. log_timezone is independent of TimeZone and controls only the timestamps in the log, so a server whose logs read local time while sessions run UTC will have you correlating incidents across an offset twice a year; set it deliberately. And now() is transaction-start time, not clock time — inside a long transaction it freezes, which is either exactly what you want for consistency or a billing bug, depending on whether you knew.
What are the three rules that keep time boring?
First, store instants as timestamptz everywhere, and treat timestamp without time zone as a code smell unless the value genuinely has no zone — a store's printed opening hour is wall clock; a created_at never is. Second, pin the session zone at connect, and express every calendar boundary — day, week, month buckets — with an explicitly named zone in the query, so the answer does not depend on who asked. Third, test the boundaries you fear: set the session zone, insert across a transition, and assert bucket counts, because no amount of reading the docs substitutes for watching a twenty-five-hour day happen in a test you control. Since adopting those three, our DST weekends have produced no billing tickets — the days still have twenty-three and twenty-five hours, we just stopped pretending otherwise.
Watching time-based pipelines with MonPG
Time-zone bugs surface as data anomalies before they surface as errors: a daily rollup whose row count spikes or dips on two specific Sundays a year, a per-day sum that drifts against its own trailing average, a job whose runtime doubles because its window doubled. MonPG tracks the operational series around those pipelines — per-statement latency, job durations, table growth — as part of its PostgreSQL monitoring, which makes the twice-a-year anomaly a visible blip against the deploy calendar instead of a customer complaint. Store instants, name your zones, and let the twenty-five-hour day be someone else's incident review.