Database datetime types serve different purposes. DATE stores just a date (2026-05-15), TIME stores just a time (14:30:00), TIMESTAMP combines them (2026-05-15 14:30:00). PostgreSQL's TIMESTAMPTZ (TIMESTAMP WITH TIME ZONE) is the special type that converts inputs to UTC for storage and back to the session's zone on read.
The most important design question is whether each datetime needs time zone semantics. Event timestamps (logs, transactions) represent absolute moments and should be TIMESTAMPTZ. Birthdays and anniversaries are zone-independent dates and DATE is enough. Business hours like "9:00-18:00" use TIME without a zone, but the relevant zone must be tracked elsewhere. Choose by meaning, not by habit.
For hotel and restaurant reservations, the booking time is meaningful in the venue's local time. Storing "7 PM at a Tokyo restaurant" as 10:00 UTC works fine in non-DST Japan. But for a New York restaurant, the UTC offset varies seasonally, and storing as UTC risks reinterpretation later when DST rules apply differently than expected.
The fix is to store TIMESTAMPTZ alongside the venue's IANA time zone name in a separate column. At display, convert the UTC instant using the saved zone, always producing the correct local time. An alternative pattern stores the local time as a TIMESTAMP (no zone) along with the zone name, treating both as required to interpret the value correctly.
Storing future events (a conference next year, a recurring meeting months out) has unique pitfalls. If the event country changes its DST policy, a UTC-stored time no longer maps to the intended local time. Storing "March 15, 2027 10:00 New York time" as 15:00 UTC, then having the U.S. abolish DST, would shift the correct UTC to 14:00.
The remedy is to store future events as (local time, zone name) and convert to UTC on demand at display or reminder time. As the IANA database updates, conversions automatically follow the new rules. Past events that have already occurred can safely be stored in UTC because no rule change can retroactively alter what happened.
Audit log timestamps may serve as legal evidence of event order, so they require the strictest design. Use TIMESTAMPTZ generated by the database server's NOW() function rather than the application server's clock. This prevents tampering by clients with control over their own clocks, which would otherwise be a credibility risk.
In distributed systems, multiple database nodes can have slightly differing clocks. When strict ordering is required, supplement timestamps with a monotonically increasing sequence number, or adopt the Hybrid Logical Clock (HLC) approach. Financial regulations like MiFID II require microsecond-precision UTC-traceable timestamps, so NTP/PTP synchronization monitoring becomes part of the operational design rather than an afterthought.
Migrating systems that were built without time zone awareness starts with identifying "what zone does this data implicitly assume?" Systems built in Japan often assume JST; systems on AWS often assume UTC; mixed origins are common. Document the assumed zone for each column before changing anything.
The safe procedure is to add a new TIMESTAMPTZ column, convert and copy existing data with the appropriate offset, switch the application to read from the new column, verify, and finally drop the old column. Pay special attention to data from DST transitions during the bulk conversion, particularly during fall-back overlap windows where two interpretations exist for the same wall-clock time.
Was this article helpful?
Python 3.9 introduced zoneinfo as the standard library solution for IANA time zones, but the long-established pytz package remains in many codebases. This article compares the two, explains the naive vs aware boundary, and covers practical pitfalls including Windows tzdata, the fold attribute, and migration strategy.
MySQL timezone bugs almost always trace back to a misunderstanding of three layers: server defaults, session settings, and column types. This article explains why TIMESTAMP and DATETIME behave differently, how the time_zone variable interacts with connections, and how to configure JDBC drivers correctly.
The cardinal rule of timestamp handling in software is store in UTC, display in local. This article covers the rationale, ISO 8601 formatting, JavaScript and Python patterns, the most common bugs, and a testing strategy that catches DST and date-line issues before they reach production.