Time — timestamptz and the Time Zone Trap

timestamptz does not store a time zone. It stores one moment, as microseconds relative to a fixed epoch in UTC, and it converts on the way in and on the way out using the session's TimeZone setting. That is almost always what you want. timestamp stores wall-clock digits with no notion of which clock produced them — half the ORMs create one by default, and every migration written in a hurry inherits it.

Both types are 8 bytes with microsecond resolution and a range from 4713 BC to 294276 AD. The storage is identical; the difference is entirely in what the value means, and therefore in what the database is allowed to do on your behalf. Nadia's audit finds orders.placed_at, customers.created_at and delivery_events.occurred_at all declared timestamp, with the application writing UTC into them as a matter of team convention.

What Each Type Stores

A timestamptz input is interpreted in the session's zone, or in the offset written into the literal, converted to UTC, and stored. The originally stated or assumed time zone is not retained anywhere. On the way out the stored moment is rendered in whatever zone the reading session asks for.

SET TimeZone = 'UTC';
SELECT placed_at FROM orders WHERE id = 981274461;
 2026-08-13 06:12:44.318+00

SET TimeZone = 'Europe/Berlin';
SELECT placed_at FROM orders WHERE id = 981274461;
 2026-08-13 08:12:44.318+02

Nothing on disk changed between those two queries. One row, one instant, two readings of the same instant — and the offset in the output is a rendering artefact, not a stored field. That is the property that makes timestamptz safe to compare, sort, index and aggregate without anyone agreeing on a convention first.

A timestamp column keeps the digits it was handed and can never say what moment they denote. Nothing rejects a value of the right shape, so the reading 02:30:00 written on 29 March 2026 is stored, indexed and returned like any other — and in Europe/Berlin the clock jumps straight from 02:00 to 03:00 that morning, so no such moment exists.

UTC in a timestamp Is Not the Same Thing

Cartwheel's team will say, correctly, that they only ever write UTC into placed_at. The bytes are therefore right. What is missing is the declaration: the type no longer states the convention, so every comparison with now(), every AT TIME ZONE, every reporting client and every future service has to re-apply a rule the database is not enforcing. It holds until one component forgets, and the result is not a crash but a set of rows two hours out.

SET TimeZone = 'UTC';

ALTER TABLE orders
  ALTER COLUMN placed_at TYPE timestamptz;

Since 12, changing between timestamp and timestamptz avoids the table rewrite when the session time zone is UTC, because in UTC the two types are binary compatible. The statement still takes an ACCESS EXCLUSIVE lock, briefly, and finishes in milliseconds instead of rewriting 40 million rows.

AT TIME ZONE, in Both Directions

The same operator does two opposite jobs, and which one it does is decided by the type of its left operand. Applied to a timestamptz, it answers "what did the clock in that zone read at this moment" and returns a timestamp. Applied to a timestamp, it asserts "these digits were a clock reading in that zone" and returns the timestamptz for the moment they denote.

-- moment  →  local wall clock  (timestamptz → timestamp)
SELECT placed_at AT TIME ZONE 'Europe/Berlin' FROM orders WHERE id = 981274461;
 2026-08-13 08:12:44.318

-- wall clock  →  moment  (timestamp → timestamptz)
SELECT timestamp '2026-08-13 08:12:44.318' AT TIME ZONE 'Europe/Berlin';
 2026-08-13 06:12:44.318+00

Reading those two lines as "the same conversion" is the source of most of the confusion around this operator. The analytics dashboard needs the first direction — grouping deliveries by the customer's local day means converting the stored moment to the customer's wall clock and truncating that, not casting the moment and hoping the server's setting happens to match. The type of the result is the tell: one direction loses the zone, the other supplies it.

The now() Family

Postgres has three different notions of "now" and they are not interchangeable. now() and CURRENT_TIMESTAMP return the start time of the current transaction and do not advance while it runs; the documentation calls this a feature, so that every modification inside one transaction bears the same timestamp. statement_timestamp() is the start of the current statement. clock_timestamp() is the real wall clock at the instant of the call, and it changes even within a single statement.

BEGIN;
SELECT now(), clock_timestamp();
 2026-08-13 06:12:44+00 | 2026-08-13 06:12:44.002+00

-- ten seconds pass, still inside the same transaction
SELECT now(), clock_timestamp();
 2026-08-13 06:12:44+00 | 2026-08-13 06:12:54.117+00
COMMIT;

Timing a long transaction with now() therefore reports zero, every time, and clock_timestamp() is what the question "how long did that take" actually wants. The stability of now() is deliberate and Cartwheel depends on it: checkout writes orders.placed_at and the first row of delivery_events in one transaction, and both land on the identical instant because both called now(). Write those two stamps with clock_timestamp() and they differ by however long the checkout took.

Dates, Intervals, and the Delivery Window

date is 4 bytes at one-day resolution with no zone at all. interval is 16 bytes and stores three separate fields — months, days and microseconds — because a month is not a fixed number of days and, across a daylight-saving transition, a day is not a fixed number of hours. Keeping them apart is what lets Postgres get calendar arithmetic right.

SELECT date '2026-01-31' + interval '1 month';   -- 2026-02-28 00:00:00
SELECT date '2026-01-31' + interval '30 days';   -- 2026-03-02 00:00:00

Adding one month clamps to the last day that exists in the target month; adding thirty days counts days. Both are right, they differ by two days, and an application that computes either in Python is reimplementing a calendar that already ships with the database — and getting the daylight-saving cases wrong on the two weekends a year when the offsets move.

-- casts every one of 40 million rows, then compares
WHERE placed_at::date = DATE '2026-08-13'

-- compares the raw column, so the index on placed_at is usable
WHERE placed_at >= TIMESTAMPTZ '2026-08-13 00:00+02'
  AND placed_at <  TIMESTAMPTZ '2026-08-14 00:00+02'

The first form applies a function to the column, so the index on placed_at cannot answer it and Cartwheel sequential-scans the whole table for one day's orders. The half-open range compares the stored value directly and uses the index. Building an expression index to rescue the first form does not work either: casting a timestamptz to date depends on the session's TimeZone, which makes the expression stable rather than immutable, so the zone has to be written into the expression before it can be indexed at all.

Storing the Zone When the Zone Is Data

There is one honest use for timestamp, and it is narrower than people think. "The depot opens at 09:00 local time, whatever that turns out to be" is not a moment yet — if a government moves that zone's offset next year, the intended instant moves with it. Storing a timestamptz would freeze a decision nobody has made. The right shape is the local wall-clock timestamp plus the IANA zone name in its own column, with the instant computed at the point of use. Cartwheel's delivery windows are not that case. A customer's Saturday slot is a concrete pair of moments, and it is stored as a tstzrange of timestamptz bounds.