Store instants as UTC
An instant is a point on the world timeline; a wall-clock reading is not. A UTC (or offset-bearing) value lets two hosts, two timezones and two DST regimes agree about what happened and in what order.
Procedure
- Declare the column type, not just the value. In Postgres use
TIMESTAMPTZ; a plainTIMESTAMPsilently drops the offset:
ALTER TABLE events ALTER COLUMN created_at TYPE timestamptz USING created_at AT TIME ZONE 'UTC';
- Serialize as ISO-8601 with an offset.
2026-03-01T14:00:00Z, never2026-03-01 14:00:00:
date -u +%Y-%m-%dT%H:%M:%SZ
- In the application layer, produce aware objects and convert only when formatting:
from datetime import datetime, timezone
now = datetime.now(timezone.utc) # aware, UTC
row = now.isoformat().replace("+00:00", "Z") # 2026-03-01T14:00:00Z
- Store the original zone name separately if you ever need to redisplay in the user's local time — UTC loses it. Keep
created_at timestamptzpluscreated_tz text('America/New_York'). - Reject writes that carry no offset at the API boundary. A regex gate on ingress:
rg -n '\d{4}-\d{2}-\d{2}[T ]\d{2}:\d{2}:\d{2}(?!Z|[+-]\d{2}:?\d{2})' payloads/ # finds bare local stamps
- Migrate legacy local strings by pinning the zone they were written in, not by assuming UTC:
UPDATE events SET created_at = created_at AT TIME ZONE 'America/New_York' WHERE created_at_was_local;
- Verify round-trip: write an instant, read it back, and confirm the offset survives.
Pitfalls
--A naiveTIMESTAMPcolumn read by a driver set to a differentTimeZonereturns a different wall clock for the same row. The bug looks like "the date changed" with no code change.new Date("2026-03-01")in JavaScript parses date-only as UTC midnight;new Date("2026-03-01T00:00")parses as local. Two spellings, two instants.- Emitting UTC but truncating the
Zproduces a string that a downstream parser reads as local. Keep the offset suffix. - Sorting a mixed column of
Zand+05:00strings lexically reorders events across zones; sort parsed instants, not text.
Verification
psql -c "SELECT count(*) FROM events WHERE created_at IS NOT NULL AND pg_typeof(created_at)::text <> 'timestamp with time zone';"
Returns 0 = every stored timestamp is offset-aware. Report: "events.created_at is timestamptz, 0 naive rows; API rejects offset-less strings."