Dates look like the simplest data type, until an application starts running in several countries, or until the clocks change. Orders that appear on the wrong day, reminders sent an hour early, daily reports that do not add up: almost always the cause is a time saved without knowing which zone it referred to. A few rules, applied from the start, avoid most of these problems.
Two different kinds of information
Before choosing a column type, it is worth distinguishing what you are recording.
- A precise instant in time. The moment an order was paid, a user logged in, a sensor measured a temperature. It is the same instant all over the world, even though Rome reads 15:00 and New York 9:00.
- A «civil» date or time. A birthday, a contract's expiry date, a shop opening at 9. The birthday is 12 May wherever the person happens to be; the shop opens at 9 local time in both winter and summer.
Confusing these two cases is the source of most errors.
Rule 1: instants are stored in UTC
UTC, Coordinated Universal Time, is the reference that has no daylight saving and never changes. Storing instants in UTC makes all values comparable, wherever they were generated, and solves the clock-change problem.
In PostgreSQL the timestamp with time zone type (abbreviated timestamptz) does exactly this: it receives a time with its zone, converts it to UTC for storage and converts it back to the session's zone when read. Despite the name, it does not keep the original zone.
CREATE TABLE logins (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id BIGINT NOT NULL,
happened_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
In MySQL the TIMESTAMP type converts to UTC based on the connection's time zone, but has a limited range ending in 2038; DATETIME, by contrast, stores the value as is, with no conversion. With DATETIME, the convention of always writing UTC must be enforced by the application.
Rule 2: civil dates stay civil dates
A birthday should be stored as a DATE, with no time and no zone. Storing it as midnight of a UTC instant leads to the classic error: a user in a zone west of Greenwich sees the birthday moved to the day before.
The same applies to recurring times, such as «opens at 9:00»: you store a local time plus the reference zone, not an instant.
Rule 3: zones are identified by name, not by offset
+01:00 is not a time zone: it is an offset valid at a certain moment. Italy is at +01:00 in winter and +02:00 in summer time. The real zone is identified by a name from the IANA database, such as Europe/Rome, which contains all the historical and current rules on clock changes.
When you need to know which zone a user or event was in, store the name in a separate column:
ALTER TABLE users ADD COLUMN time_zone TEXT NOT NULL DEFAULT 'Europe/Rome';
And convert when reading:
SELECT happened_at AT TIME ZONE u.time_zone AS local_time
FROM logins l JOIN users u ON u.id = l.user_id;
The daylight saving traps
In European Union countries, summer time starts on the last Sunday in March and ends on the last Sunday in October. This creates two special situations in local time:
- the hour that does not exist: in spring the clocks jump from 2:00 to 3:00, so a local time such as 2:30 on that day does not exist;
- the hour that repeats: in autumn they go back from 3:00 to 2:00, so 2:30 happens twice.
If local times are stored without a zone, on the autumn night two events an hour apart can appear simultaneous, and the order of events becomes ambiguous. With instants in UTC the problem does not arise.
Another consequence concerns calculations: a local day does not always last 24 hours. The day of the clock change lasts 23 or 25. Adding «one day» to an instant and adding «24 hours» do not always give the same result.
Daily reports: which day?
«Yesterday's sales» depend on the time zone. For an Italian company, yesterday runs from midnight to midnight Rome time, not UTC. The correct query converts the boundaries of the local day into instants:
SELECT SUM(total)
FROM orders
WHERE paid_at >= TIMESTAMP '2025-03-10 00:00' AT TIME ZONE 'Europe/Rome'
AND paid_at < TIMESTAMP '2025-03-11 00:00' AT TIME ZONE 'Europe/Rome';
The half-open interval, with >= at the start and < at the end, avoids counting events exactly at midnight twice and also works on 23- or 25-hour days.
A checklist
- For each time column, decide whether it is an instant or a civil date.
- Instants in a type that normalises to UTC, or in UTC by documented convention.
- Dates without a time in the
DATEtype. - Zones stored by IANA name when needed.
- Conversion to local time only at display time.
- Server, database and application configured explicitly, without depending on the system default.
In short
- Instants and civil dates are different information and need different types.
- Instants are stored in UTC; conversion to local time happens only on display.
- A zone is identified by a name such as
Europe/Rome, not a fixed offset. - Daylight saving creates non-existent or repeated local times and 23- or 25-hour days.
- Daily reports must use the day's boundaries in the business's time zone, with half-open intervals.
