Storing a future date: why UTC is not enough

I live in Miami and work with Paris. Six hours apart, except for a few weeks a year. The United States changes clocks on the second Sunday of March and the first Sunday of November, Europe on the last Sunday of March and the last Sunday of October. In 2026 the gap dropped to five hours from March 8 to 28, and it will again from October 25 to 31. Those weeks, an invite worked out in your head is easily off by an hour.

Clock changes are only the predictable part of the problem, though. The rules themselves change, decided by governments, sometimes with a few days’ notice. And that is where the most common advice about dates, “store everything in UTC”, stops being true.

For the past, UTC is right

A payment, a login, a sent message: these happened at a precise instant, and UTC describes an instant without ambiguity. Store the instant, convert it for display in the viewer’s time zone, and no political decision can change the past.

For the future, local time is the truth

A dentist appointment next Tuesday at 10:00 is not an instant. It is a time on the clock of a place. The matching instant depends on the time zone rules in force that day, and those rules can change between the booking and the appointment.

Converting to UTC at booking time freezes today’s rules into the database. The day they change, the stored instant is still there, perfectly precise, and wrong. Pick a city, then apply the rules update:

Three real cases

Mexico abolished daylight saving time in autumn 2022, only days after the law was passed. An appointment booked during the summer for July 2023, at 10:00 Mexico City time, had been converted with the old rule, UTC−5. After the reform Mexico City stays on UTC−6 all year: the stored instant lands at 9:00.

Egypt did the opposite in spring 2023 by bringing daylight saving time back, announced a few weeks ahead. Appointments for the following summer, converted with UTC+2, now show up an hour late. And on 1 March 2024, Kazakhstan moved the whole country to UTC+5: Almaty, which was on UTC+6, went back an hour.

These changes reach machines through the IANA time zone database, released several times a year, then through system, language and browser updates. There is always a delay between the decision and the server. One more reason not to convert too early.

What I store

For a future event at a local time, three columns. The local time, with no zone. The place’s IANA id, like America/Mexico_City, never an abbreviation like CST or an offset like -06:00: an offset is exactly what changes. And the computed instant, handy for sorting and queries, but treated as a cache.

create table meetings (
  id           bigint generated always as identity primary key,
  starts_local timestamp   not null, -- wall-clock time, no zone
  time_zone    text        not null, -- IANA id, e.g. 'America/Mexico_City'
  starts_at    timestamptz not null  -- derived instant, recomputed when tz rules change
);

The instant is recomputed on read, or by a job that runs after every time zone data update. That job can also compare the old instant with the new one and notify the people affected.

from datetime import datetime
from zoneinfo import ZoneInfo

def resolve(starts_local: datetime, time_zone: str) -> datetime:
    """Turn a wall-clock time into an instant, with the rules installed today."""
    return starts_local.replace(tzinfo=ZoneInfo(time_zone))

meeting = resolve(datetime(2027, 7, 12, 10, 0), "America/Mexico_City")
meeting.isoformat()                                   # '2027-07-12T10:00:00-06:00'
meeting.astimezone(ZoneInfo("Europe/Paris")).isoformat()  # '2027-07-12T18:00:00+02:00'

The cases left to decide

Storing local time solves the main problem and surfaces others, which are better decided explicitly than discovered in production.

I ran into several of these cases while building the SDKs for a timetable API, where thousands of venues publish their times in local time, day by day, each in its own zone. On the JavaScript side, the Temporal API, now reaching browsers, finally separates the two notions: PlainDateTime for wall-clock time, ZonedDateTime for wall-clock time tied to a place.

The short rule

• Past: a UTC instant.
• Future at a local time: local time + IANA id, with the instant as a cache.
• Never a fixed offset to represent a place.
• Time zone data is a dependency: update it, then recompute.

Time is still the most treacherous domain I know. But most bugs come from a single confusion, between an instant and a time shown on a clock.