How should I store dates and times in a database?
Store an instant as UTC — a Unix timestamp or a timestamp with time zone. Store a future appointment as local time plus its IANA zone name, because the offset for that zone may change before the date arrives.
Updated 2026-08-22
An instant is not a date
Most date bugs come from treating two different things as one. An instant is a specific moment on the universal timeline — when a payment cleared, when a row was written, when a message was sent. A civil date and time is a label people use locally, and it means different instants in different places.
"2026-03-15 09:00" is not a moment. It is a moment in Tokyo and a different moment in Berlin, nine hours apart. Storing that string without a zone loses the information needed to know which one was meant, and the loss is silent: the value looks complete and reads back fine.
For an instant, store UTC. A Unix timestamp or a timestamp-with-time-zone column both work, and both are unambiguous because they name a single point that every system agrees on. Convert to the viewer's local zone only at the moment of display, which is a presentation concern rather than a storage one.
This is the rule that covers the large majority of what an application records, because most timestamps are answering "when did this happen?" — and the answer to that question does not depend on where you are standing when you ask it.
Where UTC is the wrong answer
The exception matters, and getting it wrong produces bugs that appear months after the code ships.
A future appointment is not an instant. A dentist appointment at 9am on 15 March is at 9am local, whatever the offset turns out to be that day. If you convert it to UTC when it is booked, you have frozen today's offset into a date that may have a different one — and if the government changes its daylight saving rules in the meantime, which happens somewhere in the world most years, your stored instant now points at the wrong local time.
For anything scheduled in the future, store the local date and time together with the IANA zone name, such as "Europe/Berlin" or "America/New_York". Compute the instant when you need it, using a current time zone database. That way a rule change is picked up automatically rather than baked in.
Store the zone name, not the offset. "+01:00" is what the offset happened to be at one moment; "Europe/Berlin" is a rule that knows about every transition, past and future. Abbreviations are worse still — CST is used for at least three different zones, and IST for three more.
A plain date with no time at all — a birthday, an invoice date, a public holiday — should be stored as a date type and never given a time or a zone. Converting it to a timestamp is what produces the classic bug where a birthday shifts by a day for users west of the meridian.
Daylight saving is where it breaks
Twice a year, local time does something that breaks assumptions built into a surprising amount of code.
In spring, an hour does not exist. If the clocks jump from 01:59 to 03:00, then 02:30 is not a valid local time on that date. Code that constructs it gets either an error or a silent adjustment, depending on the library, and a job scheduled for that hour may not run at all.
In autumn, an hour happens twice. 01:30 occurs once before the change and once after, an hour apart, and a local time alone cannot say which. A scheduled task fires twice, an hourly report double-counts, and a duration calculated by subtracting two local times is off by an hour.
This is also why you should never compute an elapsed duration by subtracting local times. Subtract instants. The gap between 01:00 and 04:00 local can be two, three or four hours depending on the date, and the arithmetic gives no hint that anything unusual happened.
The related trap is assuming a day is always 86,400 seconds. On transition days it is 82,800 or 90,000 in most of the world, and several zones use offsets that are not whole hours — India is +05:30, Nepal +05:45, and Chatham Island +12:45. Any code that treats offsets as integers is already wrong for a few hundred million people.
Formats and a short checklist
Use ISO 8601 for anything crossing a boundary: an API response, a log line, a file name. "2026-08-22T14:30:00Z" sorts correctly as a string, is unambiguous about its zone, and is parsed by every language without a custom format string. Locale-formatted dates in an interchange format are how "03/04/2026" becomes a support ticket.
Unix timestamps are ideal for storage and arithmetic and poor for anything a human reads or debugs. The unit confusion is worth guarding against explicitly: JavaScript and Java count milliseconds while C and Python count seconds, and mixing them lands you either in 1970 or tens of thousands of years in the future.
The checklist. Store instants as UTC. Store future local events as local time plus an IANA zone name. Store plain dates as dates. Convert to local only for display. Never subtract local times to get a duration. Never store an offset where a zone name belongs. Keep the time zone database updated, because it changes several times a year and an out-of-date copy is a bug waiting for a specific date.
One thing UTC does not give you is a reliable measure of elapsed time. The system clock can be stepped backwards by a time sync, so subtracting two readings can produce a negative duration on a machine that has simply been corrected. For measuring how long something took, use a monotonic clock — performance.now in a browser, a monotonic clock source on a server — and keep wall-clock time for recording when it happened.
One more that is easy to forget: set your servers to UTC. It costs nothing, removes an entire class of "works on my machine" difference, and means the logs from every host in a fleet can be read against each other without arithmetic.