Time Zones in Databases: Why Your Timestamps Are Lying

The Session That Lasted Zero Minutes

A user in New York logs in at 1:30 AM and logs out an hour later. Your analytics dashboard says the session lasted zero minutes. Another report shows the logout happening before the login for a handful of users. Nobody touched the code.

The date was the first Sunday in November, the night US clocks fall back. At 2:00 AM Eastern, the clock goes back to 1:00 AM, so 1:30 AM happens twice. Your created_at column stored "2026-11-01 01:30:00" for both events, with nothing to say which 1:30 it meant.

This is the most common way timestamps lie: the database stored exactly what you gave it, and what you gave it was ambiguous. Most time bugs come from mixing up three different things that all look like "a time."

Three Kinds of Time

Before choosing a column type, decide which of these you're storing.

Most events your backend records are instants. Most things users schedule are wall times in a zone. The bugs come from storing one as if it were the other.

What Your Database Actually Stores

Column type names make this harder than it needs to be, because they're misleading in both major databases.

The trap in that table is the name timestamp with time zone. It doesn't store a time zone. It stores an instant, and uses the zone only to figure out which instant you meant. That's usually what you want, but if you need to know later that the user was in Karachi, you have to store that separately.

Watch the Bytes Change Meaning

The simulator writes two events, a login and a logout one hour apart, during the New York fall-back hour. Pick a column type, the time zone of the session that writes, and the zone of the session that reads.

Start with the defaults: a timestamp column, a New York writer, a UTC reader. Both rows are off by four or five hours, and the session length is wrong. Then try a few combinations:

• Writer and reader both in New York. The offsets look right now, but the session is still zero minutes long. Both events were stored as 01:30, and that information can't be recovered.
• Writer and reader both in Karachi or both in UTC. Everything is correct, because neither zone has DST. This is why the bug never shows up on your laptop or your UTC staging server.
• Switch to timestamptz. Every combination is exact. The stored value is UTC, and each reader sees the same instant in their own zone.

A naive timestamp column is only correct if every writer and every reader agrees on the zone, forever. That includes app servers, cron hosts, analytics tools, and the engineer running a query from their laptop. A timestamptz column doesn't need that agreement.

Rule 1: Store Instants as Instants

For anything that already happened, like created_at, paid_at, or last_login_at, use an instant type: timestamptz in PostgreSQL, TIMESTAMP in MySQL if the 2038 limit is acceptable (otherwise DATETIME with a strict convention that it always holds UTC), and datetimeoffset or UTC datetime2 in SQL Server.

Then make UTC the default everywhere it can leak in:

Rule 2: Store Future Wall Times With Their Zone

Instants work well for the past. For the future, they can be wrong.

Say a user in Mexico City booked a recurring 9:00 AM meeting in 2022 for dates in 2023. If you converted each occurrence to UTC at booking time, you used the DST rules in force then. In late 2022, Mexico abolished daylight saving time for most of the country. Every converted occurrence in what would have been summer time was now off by an hour, and the user never changed anything. Egypt brought DST back in 2023 with about two months' notice. Changes like these happen somewhere almost every year, and the IANA database ships several releases a year to keep up.

The user didn't mean "14:00 UTC." They meant "9:00 AM Mexico City time," whatever that turns out to be. So store what they meant:

appointments
local_start   timestamp     -- 2027-06-14 09:00 (wall time, no zone)
time_zone     text          -- 'America/Mexico_City' (IANA name, not '-06:00')

-- compute the instant when you need it, with current rules:
SELECT local_start AT TIME ZONE time_zone AS starts_at FROM appointments;

This is one of the few places a zone-less timestamp column is correct, because it's paired with a zone column that gives it meaning. If you also need an indexed instant for queries like "what starts in the next hour," store a computed UTC column and recompute it when your tz data updates.

Rule 3: "Today" Depends on Where You Are

The other place time zones show up is in queries. "Orders per day" is ambiguous until you say whose day. Grouping timestamptz values by their UTC date puts a 9:00 PM Los Angeles order on the next day.

SELECT (created_at AT TIME ZONE 'America/Los_Angeles')::date AS day,
     count(*)
FROM orders
GROUP BY 1
ORDER BY 1;

Two details are worth knowing. First, AT TIME ZONE converts in both directions: applied to a timestamptz it returns a wall time in that zone, and applied to a timestamp it returns an instant. Second, days in DST zones aren't always 24 hours long; the transition days have 23 or 25. If you compute "yesterday" as now() - interval '24 hours', you'll occasionally miss or double count an hour. Use calendar arithmetic in the right zone instead.

A range filter like WHERE created_at >= '2026-11-01' has the same problem: that literal is interpreted in the session's zone. Write the offset explicitly, or compute the boundaries in application code from the user's zone.

Choosing a Type

Key Takeaways

• Decide whether you have an instant, a wall time, or a date. The column type follows from that.
• timestamp with time zone doesn't store a zone. It stores a UTC instant. If you need the user's zone, add a column for it.
• A zone-less timestamp needs agreement from every writer and reader. One server in local time, or one DST night, is enough to corrupt it.
• Run everything in UTC (database, app servers, containers) and convert only at display time.
• Store future local events as wall time plus an IANA zone. DST rules change, and a UTC value computed today can be wrong next year.
• Offsets aren't zones. -05:00 doesn't know about daylight saving; America/New_York does.
• Group and filter by day in an explicit zone. "Today" depends on where you're standing.

References

• PostgreSQL docs: Date/Time Types, including how offsets are ignored for timestamp
• MySQL docs: The DATE, DATETIME, and TIMESTAMP Types
• IANA Time Zone Database
• Jon Skeet, Storing UTC is not a silver bullet, on future events and rule changes