The report is off by an hour: storing dates, time zones and DST correctly

When the same order shows 2:30 PM in the admin panel, 11:30 AM in the confirmation email and lands on yesterday's row in the report, the cause is almost always one design decision: the system treated time as a single kind of value. The working rule fits in three lines. Store something that already happened as a UTC instant. Store a future appointment as local wall time plus an IANA zone identifier such as Europe/Istanbul. Store calendar dates like a birth date or an invoice due date without any time component at all. Most date bugs are what happens when those three get mixed into one column.

Three kinds of time, three different columns

The first is an instant: the moment an order was placed, a payment cleared, a record changed. It is the same moment everywhere on the planet, and the time zone only affects how you display it. Store it in UTC.

The second is a calendar date or wall-clock time: a birth date, a contract start, a hotel check-in date, a due date. These have no time zone. A system that converts a birth date to a timestamp at UTC midnight will show the previous day to a user in UTC-5. It is a quiet bug, because it never reproduces on a development machine sitting in the same offset as the database.

The third is a future local time: "meeting at 10:00 on 3 November in Ankara." That is not an instant. Which absolute moment it lands on depends on the rules staying the same until then, and rules do not always stay the same.

UTC does not fix future appointments, it breaks them

Turkey dropped daylight saving time in September 2016 and moved permanently to UTC+3. Picture a booking system that had already stored a November appointment before that decision. Saving it as "07:00 UTC" was correct at the time: Turkey would be on UTC+2 in November, so the local rendering was 09:00. After the rule changed, the stored instant stayed the same and the local rendering shifted to 10:00. Nobody goes back and fixes rows in a database when a government moves a clock.

The fix is to make the local time and the zone identifier the source of truth for anything scheduled in the future, and treat the UTC value as derived. You still get a UTC column to sort and index on, and when the rules change you recompute it. For past events the relationship inverts: they already happened, so the UTC instant is the fact and the local rendering is derived.

A zone identifier is not an offset

+03:00 is an offset, not a rule. It describes one moment and tells you nothing about what happens in that place next spring. A zone identifier like Europe/Istanbul or America/Sao_Paulo carries the region's entire history and its currently known future. Store the identifier on the user profile, not the offset.

Identifiers are not permanent either. Release 2022b of the tz database renamed Europe/Kiev to Europe/Kyiv, keeping the old name as a backward-compatible alias. That is a good reason not to copy the zone list into your own table and freeze it there. Read the list your platform provides, and resolve aliases when you compare values.

The tz database is a dependency, and every layer has its own copy

The IANA tz database is updated as governments change the rules, several releases a year. The first half of 2026 brought 2026a in March, 2026b in April and 2026c in July. The churn is real: Brazil abolished DST in 2019, Iran and most of Mexico did the same in 2022, while the European Union still moves its clocks on the last Sundays of March and October despite a proposal to stop that has been sitting unresolved since 2018.

The awkward part is that this database exists separately in the operating system, the language runtime, the database server and the browser, and those copies can be at different versions. A container image that has not been rebuilt in a year runs on stale rules. It does not throw an error, it just computes the wrong answer. MySQL adds one more trap: if the time zone tables were never loaded into the server, functions that convert between named zones will not behave the way you expect.

Picking the right database type

In PostgreSQL, timestamptz stores values in UTC and, despite the name, does not remember the zone they came from. It converts on input and renders in the session's TimeZone setting on output, which makes it the right type for instants. Plain timestamp performs no conversion at all, which is what you want for wall-clock values.

In MySQL, TIMESTAMP is converted to UTC using the session time zone and DATETIME is not. The sharper constraint is the range: TIMESTAMP tops out at 2038-01-19 03:14:07 UTC. A fifteen-year lease end date or a long-dated loan schedule already crosses that line today. DATETIME runs to the year 9999 but does no conversion, so the zone handling becomes your problem to solve in application code.

For scheduled future events, a three-column pattern removes the ambiguity: local wall time, zone identifier, and a computed UTC instant. Keeping all three lets you query efficiently and repair the derived column when a rule changes.

Times that never happen and times that happen twice

On the night clocks move forward, local time jumps to 03:00 and 02:30 simply does not exist. On the night they move back, 02:30 happens twice. Libraries disagree on how to handle both cases: some raise an error, some shift forward, some pick the earlier occurrence. If you have not chosen a disambiguation policy deliberately, you have inherited whatever your library does by default.

For scheduled work this turns into a production incident on a predictable schedule. A nightly job set to run at 02:30 local time will skip a run once a year and run twice once a year. Keeping servers and schedulers on UTC is the first defense; writing jobs so a duplicate run causes no damage is the second. We went through that kind of replay safety in the piece on background jobs and queues.

The report's day boundary belongs to the business, not the server

"How many orders did we take yesterday" depends entirely on which midnight starts yesterday. If the server runs on UTC and the business runs in Istanbul, every order placed between 00:00 and 03:00 local time lands in the previous day's bucket. At month end that is enough to make the revenue table disagree with the accounting system.

The fix is definitional rather than technical. Write the zone used for the day boundary into the metric definition itself, then build the query from that definition. Where those definitions should live is the subject of our article on reporting consistency.

Use the right types in application code

Java's java.time package already made the distinction for you: Instant for a moment, LocalDate for a calendar date, ZonedDateTime for a local time with rules attached. Python has had zoneinfo in the standard library since 3.9, and naive datetime objects with no zone information should not cross your application boundaries.

JavaScript was the weak link for a long time, because Date only understands UTC and the browser's local zone. Temporal changes that: it reached Stage 4 at the TC39 meeting in March 2026, Chrome 144 shipped it in January 2026 and Firefox 139 shipped it back in May 2025, but Safari has not shipped it in a stable release yet. So production code still needs a polyfill. If all you need is formatting, passing a timeZone option to Intl.DateTimeFormat covers most cases without any of this.

Every timestamp that crosses a boundary carries its zone

In APIs and file transfers, send timestamps as ISO 8601 / RFC 3339 with the offset attached: 2026-09-09T14:30:00+03:00. A string like 09/09/2026 14:30 gets interpreted against the receiver's locale, and a good share of the "everything is one day off" tickets in integration projects start exactly there.

If you also need the zone identifier to survive the trip, RFC 9557, published in April 2024, standardized it: 2026-11-03T10:00:00+03:00[Europe/Istanbul]. Temporal serializes to that format. Adding such a field is a change to your API contract, so apply the approach from our article on versioning and backward compatibility.

You cannot be confident without testing it

None of these bugs surface on a developer machine that sits in the same fixed offset as production. You have to provoke them. Run the test suite in CI under a zone that is not UTC, and pick an unfriendly one: Pacific/Chatham, at 45 minutes off the hour, catches code that assumes whole-hour offsets immediately. Turn DST transition dates into fixtures, and keep 29 February in the set.

The prerequisite for all of it is removing direct "what time is it now" calls from your code. Once the clock is an injectable dependency, tests can freeze time at any moment and replay a transition as often as you like. Our article on test automation strategy covers which layer edge cases like these belong to.

Five checks worth running this week

1. List every date column in your schema and mark each one as an instant, a calendar date or a future local time. The mismatches become obvious as soon as they are written down.
2. Check whether any MySQL TIMESTAMP column needs to hold a value beyond 2038.
3. Look at the tz database version inside your production images and attach it to your update process.
4. Confirm whether scheduled jobs run on local time or UTC, and write down what happens to each one on a transition night.
5. Add the day-boundary zone to your report definitions so two teams cannot compute yesterday differently.

If one of those checks turns up something you cannot explain, or clocks are drifting somewhere in an existing system and you cannot find where, we can go through the schema and the data flow together and come out with a concrete remediation plan.