Timestamps and Timezones
Timestamp storage is a policy problem: store instants in UTC, name the type honestly, and convert at boundaries — naive timestamps and per-row local times are the classic outage factory.
Definition
Database timestamp types split into instant-tracking types that normalize to UTC internally (timestamptz, TIMESTAMP WITH TIME ZONE) and wall-clock types that store naive local fields. Correct policy: store absolute instants in UTC-typed columns, render per-user timezones at the edges, and never store a local time without an explicit offset or zone column beside it.
Context and scope
Hits scheduling, billing windows, audit trails, and any global application. Also the source of daylight-saving bugs, server-timezone drift, and ORM silently coercing types.
Common misconceptions
- timestamptz stores the timezone — it stores an instant normalized to UTC; no zone is recorded with the value
- Storing UTC is enough — rendering and recurring-event semantics still need the user zone, which is data you must keep
- The server clock timezone is the column timezone — session timezone affects rendering of naive types and literal parsing, not stored instants
Anti-patterns
- Storing local wall-clock times without offsets and silently shifting every value on server migration
- Computing durations by subtracting rendered strings across zones and daylight-saving transitions
- Encoding recurring schedules as fixed UTC instants instead of local-time rules that track DST boundaries
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/datatype-datetime.html
What it is
A moment in time is absolute; a wall-clock reading is location-dependent. Databases offer both shapes, and picking wrong entangles data with server or session configuration. Instant types normalize input to UTC and render according to session zone; naive types store exactly the fields given with no interpretation.
How it works
In PostgreSQL, timestamptz converts input using the session zone into UTC storage and back on output; timestamp keeps fields verbatim. MySQL converts TIMESTAMP columns using the session time_zone and stores DATETIME verbatim. ORM defaults and driver session settings are the hidden variable that changes what the same literal means.
When to standardize
Event times, audit rows, and every machine-generated timestamp: instant in UTC, zone-free logic, per-user rendering. Human-entered local times — a 9am standup — need zone or DST-rule columns so the wall clock survives zone-law changes.
Pitfalls
Durations must be computed in instants and stored as intervals, never as differences of rendered local strings. Server timezone migration with naive columns shifts all data at once — schedule a conversion, not a config change. Test with zones that have unusual offsets and DST flips, not just UTC.