Almost every painful date bug traces back to a single decision made early and never revisited: how the time was written to the database. Get that choice right and the rest of the system inherits correctness for free. Get it wrong and you spend years patching the same off by an hour ghost in reports, exports, and notifications. This is the decision, laid out as a small set of rules with the reasoning behind each one.
The one rule that prevents most of the bugs
If a moment has already happened, store it as a UTC instant. Not the local time it happened in, not the offset of the server, not whatever the user's browser sent. A single instant in UTC. The instant is the fact; the local time someone saw is a presentation detail you can always recompute later, but you can never recover the true instant from a naive local time once the zone is lost.
The reason this matters is that an instant is unambiguous and a local time is not. The string 2026-03-29 01:30 in London does not name a single moment, because the United Kingdom springs its clocks forward that morning and 01:30 happens twice in some zones and not at all in others. UTC has no such gaps or repeats, which is precisely why it makes a safe storage format. You can confirm the offset for any moment with the UTC offset globe before you trust a conversion.
An instant and a wall-clock time are not the same thing
This is the distinction that the whole topic hangs on, and most storage bugs come from collapsing the two. An instant is a point on the universal timeline: the epoch second 1774761000 is the same moment everywhere on Earth. A wall-clock time is what a clock on a particular wall reads: 09:00 on a Monday in a named city. You convert between them with a time zone, and the conversion is only stable in one direction.
Past events are instants. They occurred at a definite point on the timeline, and the local time is just a label. Future intentions are often wall-clock times. A 09:00 standup next quarter is a promise about what the clock will read, and which instant that maps to depends on rules that have not finished changing yet. Storing a wall-clock intention as a frozen instant is the classic mistake, and the next section shows exactly how it breaks.
A worked example: the standup that drifts
Suppose you build a scheduler in February 2026 and a London team books a daily standup for 09:00. The naive implementation converts that to a UTC instant once, using the offset in force the day it was created. In February, London is on Greenwich Mean Time at UTC+0, so 09:00 local becomes 09:00 UTC, and the row is saved as a fixed instant repeated each day.
On Sunday 29 March 2026, the United Kingdom springs forward: at 01:00 the clocks jump to 02:00 and London moves to British Summer Time at UTC+1. Your frozen 09:00 UTC instant now renders as 10:00 in London. The team that asked for a 09:00 standup is paged at 10:00 from the end of March until the clocks fall back on 25 October 2026, when it silently corrects itself. Nobody changed the booking. The data was wrong the moment it was stored, and the calendar simply revealed it at the transition.
The fix is to store what the user actually meant: the wall-clock time 09:00 and the zone Europe/London, as two fields. You compute the instant for each occurrence at read time, applying whatever offset is correct on that specific date. Now the spring transition is handled by the conversion, not baked into stale data, and 09:00 stays 09:00 across every daylight saving boundary. The daylight saving survival guide lists every 2026 transition date so you can test your scheduler against the exact days it is most likely to break.
Choosing the column type
Once you have decided whether a value is an instant, a wall-clock time, or a plain calendar date, the column type follows from that, not from habit.
- Instants go in a zone aware timestamp. In Postgres that is timestamptz, which stores the value as UTC and converts to the session zone on the way in and out. The name is misleading because it does not actually keep a zone; it keeps a normalized UTC instant, which is exactly what you want. Default to this for created_at, paid_at, logged_at, and anything that records something that happened.
- Future wall-clock events go in two columns. A plain timestamp for the local digits plus a text column holding the IANA zone name, for example Europe/London or America/New_York. Never store a bare numeric offset like +01:00 for a future event, because the offset is a consequence of the zone and the date, and it changes twice a year.
- Calendar dates go in DATE. A birthday, an invoice date, a contract start. No time, no zone, no fake midnight. The next section explains why a timestamp here is a trap.
- Epoch integers are a fine alternative for instants. A 64 bit integer of seconds or milliseconds since 1970 carries the same information as a UTC timestamp and is language neutral, which suits event logs and cross system message payloads. You lose readable SQL date arithmetic in exchange for compactness. The epoch converter turns these back into human dates while you are debugging.
The naive-datetime trap in application code
Even with the right column, the value can be corrupted before it reaches the database. The danger is the naive datetime: a date and time object in your language that carries no zone. When you save it, something has to decide what zone it meant, and that something is usually an invisible default like the server's local zone or the database session zone. The same code then behaves differently on a developer laptop set to local time and a production box set to UTC, which is how a bug ships green and breaks in production.
The discipline is to make time zone aware at the edges of your system and keep it that way. Parse incoming times into zone aware objects immediately, do all internal work in UTC, and attach a zone again only at the final moment you render something for a human. In JavaScript this means being deliberate about the Date object and the newer Temporal API rather than relying on the host zone; the JavaScript date and time guide walks through the specific footguns and the safe patterns. Treat any datetime without a zone as radioactive: it is not data yet, it is a question waiting to be answered wrong.
Date-only and time-only values deserve their own types
A surprising share of timezone bugs are not about times at all. They are calendar dates that were promoted to timestamps. Store a date of birth of 1990-05-14 as a timestamp and your system invents a midnight: 1990-05-14 00:00. Read that back in a zone west of UTC and midnight rolls backward across the date line, so the birthday renders as 1990-05-13. The user did not move; the fake time you attached did.
The rule is to match the type to the meaning. If the real world thing is a day on a calendar, use a DATE and never give it a time. If it is a recurring clock reading with no date, such as a shop that opens at 08:30 every day, use a TIME and pair it with the zone the shop lives in. Reserve timestamps for genuine instants. This separation also keeps your schema honest, because the column type now documents what kind of time each value is.
Querying and indexing without melting the database
Storing in UTC raises a fair question: how do you ask for everything that happened on a particular local day when the rows are all in UTC? The answer is to convert the two boundaries of that day into UTC instants and filter the raw column against that range. Suppose you want all orders placed on 21 June 2026 in New York, which is on Eastern Daylight Time at UTC-4 that day. Local midnight to midnight becomes 2026-06-21 04:00 UTC up to 2026-06-22 04:00 UTC. You then select rows where the stored UTC timestamp is greater than or equal to the first and strictly less than the second.
Two details make this fast and correct. Use a half open range, inclusive at the start and exclusive at the end, so a row at exactly the next midnight is not counted twice across adjacent days. And filter the bare stored column rather than wrapping every row in a timezone conversion function, because a function applied per row throws away the index and forces a full table scan. Convert the two boundaries once in application code or a small subquery, then let the index on the timestamp do the work. The same logic underpins the ISO 8601 converter, which is handy for sanity checking the exact boundary strings before you put them in a query.
A storage checklist
Before a date column ships, walk this list:
- Decide first whether the value is an instant, a future wall-clock time, or a calendar date. The type follows from the answer.
- Store instants as UTC in a zone aware timestamp, or as an epoch integer if you prefer.
- Store future local events as a wall-clock time plus an IANA zone name, never as a frozen UTC instant or a bare offset.
- Store calendar dates in DATE and clock readings in TIME, with no invented midnight.
- Make every datetime zone aware at the system boundary and refuse naive values inside it.
- Query by converting the day boundaries to UTC, using a half open range, and never wrap the indexed column in a per row function.
- Test against real daylight saving transition dates, not just an ordinary weekday.
Frequently asked questions
Should I store dates as UTC or as a Unix timestamp?
For a moment that has already happened, both are fine because both name a single instant. A timestamptz column stored in UTC and a Unix epoch integer carry exactly the same information, so the choice is about ergonomics: timestamptz reads as a date in your tools and supports interval arithmetic in SQL, while an epoch integer is compact and language neutral. The real mistake is neither of these. It is storing a naive local time with no zone at all.
Why is storing a future meeting as a fixed UTC instant a bug?
Because daylight saving rules and government time zone definitions change, and a future local time is only pinned to an instant once you know which rules apply on that date. If you freeze a 2026 booking to a UTC instant using today's rules and a country then shifts its clocks, the booking lands at the wrong local time. Future wall-clock events should be stored as the local time plus the IANA zone name, and converted to an instant only when needed.
What is the difference between timestamp and timestamptz in Postgres?
Despite the name, timestamptz does not store a zone. It stores a UTC instant and converts to the session zone on the way in and out, which is what you want for events that happened. A plain timestamp stores the digits you gave it with no zone meaning at all, so two rows reading 09:00 could refer to different instants. Default to timestamptz for instants, and treat plain timestamp as a wall-clock value that always needs a separate zone column.
Should I store a date of birth in UTC?
No. A date of birth, an invoice date, or a public holiday is a calendar date, not an instant, so it has no time and no zone. Store it in a DATE column. Forcing it through a timestamp adds a fake midnight that then shifts across the date boundary the moment someone reads it in another zone, which is exactly how a birthday silently moves to the day before.
How do I query rows that happened on a given local day?
Convert the boundaries, not the rows. Work out the start and end of that local day as two UTC instants, then filter the stored UTC column with a half open range from the first up to but not including the second. This lets the database use the index on the timestamp column. Wrapping every stored row in a per row conversion function instead forces a full scan and is the most common reason these queries crawl.