Why This Matters
Almost every developer has a datetime story. An appointment showing up an hour late. A report aggregating the wrong day's data. A notification firing at 3am. It works fine in development, ships, and then a user in a different timezone files a bug you can't immediately reproduce.
A 2025 empirical study of real-world bugs in open-source software found that 53.6% of datetime bugs are timezone-related, and 27.8% stem specifically from mixing naive and timezone-aware datetime objects incorrectly. These are the result of treating datetime as a trivial detail.
Datetime is one of the most common forms of silent tech debt. It's easy to defer because it appears to work until it doesn't. By the time it fails, it's in production for a user in a country you didn't test for. By then, you're stuck refactoring entire columns in a live production database.
This post is the reference I wish I'd had earlier — terminology, mental models, and the decisions that actually matter. Treat it as a cheat sheet.
The Terminology (Get This Right First)
Before anything else, let's align on words. These get conflated constantly.
| Term | What it means |
|---|---|
| UTC | The universal time reference. Every other timezone is an offset from it. Not a timezone itself — a standard. |
| Offset | A fixed shift from UTC. +04:00 means 4 hours ahead. −05:00 means 5 hours behind. |
| Timezone (TZ) | A named region with rules — including DST. Asia/Dubai is always UTC+4. America/Toronto is UTC−5 in winter, UTC−4 in summer. An offset is a snapshot; a timezone is the full rulebook. |
| Wall clock time | What the clock on the wall shows in a given place. 09:00 in Dubai. Meaningless without knowing where. |
| Naive datetime | A datetime with no timezone or offset attached. 2024-06-01T09:00:00. Could mean anything. |
| Aware datetime | A datetime with offset attached. 2024-06-01T09:00:00+04:00. Unambiguous. |
| ISO 8601 | The international standard for representing datetimes as strings. What you should always use for transfer. |
| Unix timestamp | Seconds (or milliseconds) since Epoch: 1970-01-01T00:00:00Z Always UTC. Useful for math, not for display. |
| DST | Daylight Saving Time. The reason offset ≠ timezone — the same timezone can have different offsets at different times of year. |
UTC: The One Ring
UTC is the reference point everything else orbits. It doesn't observe DST. It doesn't change. When you know a moment in UTC, you can derive the correct wall clock time anywhere in the world by applying the local offset.
Store in UTC. Display in local. That's the whole principle. Everything else is implementation detail.
ISO 8601: The Transfer Format
When moving a datetime between systems — from backend to frontend, from database to API, from one language to another — you need a string format both sides understand. That format is ISO 8601.
2024-06-01T09:00:00 ← naive (no offset) — avoid this
2024-06-01T09:00:00Z ← UTC (Z = zero offset)
2024-06-01T09:00:00+00:00 ← UTC (explicit offset)
2024-06-01T13:00:00+04:00 ← Dubai local time
2024-06-01T05:00:00−04:00 ← Toronto local time
The last four all represent the same moment in time. A system that parses ISO 8601 correctly will handle all of them identically — converting to UTC internally and displaying in the user's local timezone.
The naive format (no offset) is the one to avoid. It looks like a datetime but carries no timezone information. Every system that receives it has to guess — and they won't all guess the same thing.
How Postgres Handles This
Postgres has two datetime types that look similar but behave completely differently:
| Type | What it stores | Timezone-aware? |
|---|---|---|
date |
Calendar date only (2024-06-01) |
No |
time |
Time of day only (09:00:00) |
No |
timestamp |
Date + time, verbatim, no conversion | No |
timestamptz |
Date + time, converted to UTC internally | Yes |
Default to timestamptz for every datetime column. The overhead is negligible. timestamp is appropriate only when you can guarantee the writer and reader will always be in the same timezone — a constraint that rarely holds as products grow.
Don't Split Date and Time Into Separate Columns
This one is counterintuitive but important.
A common pattern is storing date and time separately:
appointment_date DATE, -- "2024-06-01"
appointment_time TIME, -- "09:00:00"
It seems clean. But it breaks down across timezones.
The problem: a "day" is not the same moment everywhere.
If a client books at 09:00 Dubai time on June 1st, that's 05:00 UTC on June 1st. But for a user in Toronto viewing the same record, it's 01:00 on June 1st — still June 1st, fine. But what if the appointment was at 02:00 Dubai time? That's 22:00 UTC on May 31st. The date changes depending on where you're standing.
When you store date and time as separate fields, you lose the ability to reconstruct the correct moment in time without knowing the business's timezone — information that isn't in those columns.
Use a single timestamptz column. It stores the full moment — date, time, and UTC equivalent — atomically. You can always extract the date or time for display on the client.
When date Alone Is Correct
There are cases where a plain date column is genuinely right: when the value is inherently timezone-independent.
Birthdate is the canonical example. If someone was born on June 1st, that's true regardless of what timezone you're in when you ask. It's a calendar fact, not a moment in time. Storing it as timestamptz would actually introduce a problem — you'd need to decide which timezone to use, and converting it could shift the date.
Same for:
- Public holidays (
2024-12-25is Christmas everywhere) - Contract effective dates (
2024-07-01a policy takes effect) - Expiry dates on a license or document
The test: "Does the answer change depending on where you are when you ask?" If no — use date. If yes — use timestamptz.
The Cheat Sheet
Write path (always):
- Send an offset-aware ISO 8601 string:
2024-06-01T13:00:00+04:00 - Never send a naive string to a
timestamptzcolumn — Postgres will assume UTC
Read path (always):
- For
timestamptz: the string comes back with+00:00— parse it, then convert to the user's local timezone before display - For
timestamp/date: the string comes back with no offset — treat as-is, no conversion
Never:
- Store datetime as a plain string
- Split a moment in time across separate date + time columns
- Assume the server timezone matches the user timezone
- Skip
.toLocal()(or equivalent) on display
One Last Thing: Offset vs Timezone
These are not the same, and the distinction matters if your product operates across DST boundaries.
+04:00 is an offset — a fixed number. Asia/Dubai is a timezone — a named ruleset that includes DST transitions.
Dubai doesn't observe DST, so Asia/Dubai is always +04:00. But America/Toronto is −05:00 in winter and −04:00 in summer. If you hardcode the offset, you'll be wrong half the year.
Store and reason in named IANA timezones (Asia/Dubai, Europe/Berlin, America/Toronto). Use the offset only at the moment of conversion, derived from the IANA name at that specific point in time.
Datetime isn't hard. But it requires deliberate thought — the kind most developers postpone until users are in two countries and support tickets start rolling in. Get it right from the start and it stays invisible, which is exactly where infrastructure should be.
Top comments (0)