Pimp My IDE / garage dispatch
Back to garage
September 28, 2026 | PostgreSQL / time / query review

UTC is not a spell.

In PostgreSQL, AT TIME ZONE changes both the value and its type. A review that ignores either one can pass on a UTC server and fail on a developer laptop.

Before approving time math, write down the input type, session timezone, intended calendar, and output type.

The same phrase has two directions.

PostgreSQL documents two opposite behaviors for AT TIME ZONE. Applied to timestamp without time zone, it assumes the named zone and returns timestamptz. Applied to timestamptz, it shows that instant in the named zone and returns timestamp without time zone.[2]

The words stay the same while the type decides the direction. That is enough to make a plausible patch wrong. A reviewer who sees only 'UTC' has not seen the operation.

Ask what type goes in and what type comes out.

The session timezone is an input.

When PostgreSQL compares a timestamp without a zone to one with a zone, it assumes the zone-less value belongs to the session's TimeZone setting. The documentation states this conversion rule directly.[2] The query can therefore change meaning when the session setting changes.

A current field report about monthly time-series joins ran into this class of problem. The article is useful because it shows how an explicit UTC conversion can still return a different type than the author expected.[1] Its Hacker News discussion also caught an overstatement in the post. Mixed timestamp comparisons are not always false. PostgreSQL converts the zone-less side through the session timezone, so the result depends on that setting.[4]

Storage and display are separate jobs.

The PostgreSQL wiki advises against storing UTC instants in timestamp without time zone. That type cannot identify a real instant by itself. It can still be right for a wall-clock value such as "every day at 09:00," where no instant exists until a date and zone are supplied.[3]

timestamptz does not preserve the original zone name. PostgreSQL stores an instant internally and renders it using the current session timezone.[3] If the original civil zone matters, store its name in a separate field.

Calendar arithmetic needs a named calendar.

A month is not a fixed duration. PostgreSQL 16 and later provide date_add(timestamptz, interval, zone). The third argument tells PostgreSQL which zone governs times of day and daylight-saving adjustments. If it is omitted, PostgreSQL uses the session TimeZone.[2]

That does not make every monthly query correct. It makes one hidden input visible. Decide whether the rule follows UTC, a customer's civil zone, or a stored billing calendar. Then test a month end and a daylight-saving boundary.

Make the query carry its witness.

  1. Inspect the column type with pg_typeof. Do not infer it from the name.
  2. Record current_setting('TimeZone') in the test output.
  3. State whether the value is an instant or a wall-clock reading.
  4. Assert the output type after conversion or arithmetic.
  5. Run one month-end case and one daylight-saving case in every supported zone.

The reliable review is not "convert it to UTC." It is a short receipt that names every assumption PostgreSQL would otherwise supply for you.

Interactive makeover / timezone alignment rack

Type before time

Traditional purpose replaced: paste one conversion snippet and hope. Better version: couple the stored type to the session zone, choose the operation, and copy a test receipt that exposes both.

Set the shafts

Native radio controls own the state. The axle shows whether the selected query keeps its assumptions visible.

Stored value
Session timezone
Operation under review

Teaching diagram. The rack drafts SQL checks. It does not inspect your schema or execute a migration.

Explicit instant pathBoth sides are typed as instants. Keep the session setting in the test receipt because display still depends on it.
Copyable PostgreSQL witness

Expose the hidden input

Replace events.happened_at and the sample literal with values from your schema.

What this component proves. It keeps the type, session zone, operation, and boundary cases in one test draft. It does not query a database, confirm your business calendar, or prove the result.

Sources and limits

Open the source log
  1. Book of Revenue, "Postgres AT TIME ZONE 'UTC' does NOT do what you think it does", read September 28, 2026. This field report prompted the review of type-changing conversion and monthly arithmetic. Its explanation contains a disputed claim about mixed-type comparisons, so this article relies on PostgreSQL's documentation for the rule.
  2. PostgreSQL 18 documentation, "Date/Time Functions and Operators", read September 28, 2026. The manual defines both directions of AT TIME ZONE, mixed-type comparison behavior, and the timezone argument to date_add.
  3. PostgreSQL 18 documentation, "Date/Time Types", and the PostgreSQL wiki date/time storage guidance, read September 28, 2026. These pages separate timestamp types, instant storage, session display, and the narrow uses for zone-less timestamps.
  4. Hacker News discussion for item 49865312, verified through the Hacker News API and read September 28, 2026. The discussion supplied the specific correction about session-dependent mixed-type comparison. It is commentary, not the authority for PostgreSQL behavior.

Evidence boundary. The SQL witness is a draft for PostgreSQL 16 or later when it uses the three-argument date_add form. We checked its claims against PostgreSQL 18 documentation. The page does not know your column types, session settings, business calendar, or supported server version. Run the generated query against a disposable database and replace every expected value with your real contract.