Storing Timestamps Right: Instants vs Wall Time, UTC, Time Zone Names and DST Traps

Most date and time bugs are invisible on the day they are written. Tests pass, screens show the right time, and then twice a year when clocks change a report shifts by an hour, a reminder fires twice or not at all. When a country drops daylight saving time, next month's meetings suddenly display at the wrong hour. The root cause is almost always the same: storing a moment and storing a wall clock reading were treated as the same thing.

Two different things: instants and wall time

A time value can answer two different questions.

An instant: "When did this happen on the universal timeline?" An order being placed, a session starting, a log line. It is the same point no matter where you look from. The right representation is UTC or an epoch based value.

Wall time: "What does this person's clock say?" An alarm that rings at 09:00 every morning, a meeting starting at 14:00 in Istanbul, a birthday. It only has meaning together with a time zone, and the instant it maps to changes if that zone's rules change.

The rule is short: store events that already happened as instants; store future events that follow a human calendar as local wall time plus a time zone name. Every other approach breaks this distinction, knowingly or not.

Three storage approaches and where each one is right

1. Store everything in UTC. This is the right default for past events. Sorting, range queries and comparison are cheap, and it does not matter where the server runs. For future human events it is a trap: convert "10:00 in Istanbul in June" to UTC using today's rules, and if the region's rules change before June, the stored instant no longer lands on 10:00. Rule changes are not theoretical. Countries abolish DST, adopt it, move transition dates, and the time zone database (tzdata) ships several releases a year.

2. Store an offset (+03:00). RFC 3339 expresses an instant unambiguously and is great on the wire. But an offset is not a time zone. +01:00 is Berlin in winter and Lagos all year; you cannot answer "same time next week" from an offset because it knows nothing about DST transitions. Keeping the offset while dropping the zone name loses information.

3. Store local time plus an IANA zone name (Europe/Istanbul). This is the correct model for future, recurring, calendar driven events. You compute the instant with whatever rules are current when you need it. The cost: queries and sorting get harder, and every read needs a conversion.

In practice you use both. The source of truth for a future event is local time plus zone; a derived UTC instant lives in a separate column for indexing and for driving the scheduler, and gets recomputed when tzdata is updated.

The right column type

In PostgreSQL, timestamptz does not store a time zone despite its name. It converts input to UTC, stores the instant, and renders it in the session's TimeZone on the way out. timestamp (without time zone) stores the wall time you give it with no interpretation. So: timestamptz for instants, timestamp plus a separate zone column for wall time.

CREATE TABLE event (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text        NOT NULL,
  local_start timestamp   NOT NULL,
  time_zone   text        NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

SELECT title,
       local_start AT TIME ZONE time_zone AS start_instant
FROM event;

timestamp AT TIME ZONE 'zone' interprets a wall time in that zone and yields a timestamptz instant. The reverse, timestamptz AT TIME ZONE 'zone', turns an instant into that zone's wall time. Mixing up the two directions is the most common mistake.

MySQL differs: TIMESTAMP is converted to UTC using the session zone and has a range that ends in 2038, while DATETIME stores the value as given. For long lived data, keep the TIMESTAMP range in mind.

For date only fields (birthdays, invoice dates, holidays) do not use a timestamp at all; there is a date type. Storing a birthday as midnight UTC moves it back a day for anyone in a negative offset.

In application code

Most languages encode the distinction in types, and you should use them. In Java, Instant is an instant, LocalDateTime is zone-less wall time, ZonedDateTime combines the two. Python has aware and naive datetime objects; trying to represent an instant with a naive object is half of all trouble. Since Python 3.12, datetime.utcnow() is deprecated precisely because it returns UTC as a naive object:

from datetime import datetime, timezone
from zoneinfo import ZoneInfo

now = datetime.now(timezone.utc)
in_istanbul = now.astimezone(ZoneInfo("Europe/Istanbul"))

In JavaScript, Date is always an instant (epoch milliseconds) and carries no zone. A subtle detail: a date only ISO string like new Date("2026-10-27") is parsed as UTC, while new Date("2026-10-27T10:00") is parsed as local time. The same code can show a different day on the server and in the browser.

Pin servers, containers and database sessions to UTC. That does not make code correct, but when something somewhere uses a naive value, the bug shows up identically in every environment. A team whose laptops run in one zone while production runs in UTC will start getting "works on my machine" bugs from time zones alone.

The two DST traps

In a region that observes DST, once a year some wall times do not exist and once a year some exist twice. In Europe/Berlin, on 29 March 2026 clocks jump from 02:00 straight to 03:00, so 02:30 never happens. On 25 October 2026 they fall back from 03:00 to 02:00 and 02:30 happens twice.

Consequences:

• A local time entered by a user can land in the gap. Most libraries do not raise; they shift in one direction. Python's zoneinfo silently picks an offset. A practical way to detect a nonexistent time is a round trip through UTC, checking whether you get the same wall time back:

from datetime import timezone

def wall_time_exists(dt):
    back = dt.astimezone(timezone.utc).astimezone(dt.tzinfo)
    return back.replace(tzinfo=None) == dt.replace(tzinfo=None)

• In the repeated hour it is ambiguous which one is meant. Python distinguishes them with the fold attribute, Java with ZonedDateTime.withEarlierOffsetAtOverlap() and withLaterOffsetAtOverlap(). Pick one as a product decision and document it.
• "Daily" jobs either skip or run twice on transition days. A scheduler driven by local system time may skip or repeat a 02:30 job. Schedule critical jobs in UTC or outside transition hours. Where a scheduler supports an explicit zone, such as the timeZone field of a Kubernetes CronJob, set it rather than inheriting the node's local time.
• "Add one day" is not "add 24 hours". A transition day is 23 or 25 hours long. Do calendar arithmetic on wall time, then convert to an instant.

Day boundaries in reports

"Today's orders" cannot be answered without saying whose today. A report grouped by UTC day shows a business in UTC+3 a day that closes at 03:00 local time. Group in the business's zone:

SELECT date_trunc('day', created_at AT TIME ZONE 'Europe/Istanbul') AS day,
       count(*)
FROM orders
GROUP BY 1
ORDER BY 1;

If you filter on that expression often, the index has to be built on the same expression, otherwise a plain index on created_at is not used. Usually better: convert the local day boundaries to instants in the query and write a plain created_at >= ... AND created_at < ... range.

tzdata updates are a deployment concern

Time zone rules do not live in your code. They live in the tzdata that ships with the operating system, the language runtime or the database. When a country changes its rules, the fix is a dependency update, not a code change. Keep tzdata current in your container images and runtimes, and in environments that bundle their own copy, like Java and Python, know which copy is in use. If different layers carry different tzdata versions, the application and the database can map the same local time to different instants.

When this is overkill

For a system that runs in a single region, serves a country without DST and stores no future calendar events, the local time plus zone model is unnecessary complexity. Storing everything as timestamptz and rendering in a fixed zone is enough. Even then, do not put instants and wall times in the same type: the day the system opens to another region, reverse engineering what every column meant costs far more than choosing the right type up front.

The checklist is short: instants for past events, wall time plus an IANA name for future human events, date for date only values, UTC on servers, an offset is not a zone, adding a day is not adding hours, and tzdata is a dependency.