UTC for past events doesn't always work either. For example, historical employee punch-in times.
UTC timestamps should only ever be used for points in time in the most literal sense, and nothing else.
UTC timestamps should only ever be used for points in time in the most literal sense, and nothing else.
Future timestamps should be local because local timezone changes literally change the instant the event it will happen (relative to UTC). For past things, this can’t happen
If an employee clocks in at 2026-06-22 09:00 America/Sao_Paulo time, (which has a -03:00 offset today), and the server's clock is in UTC, the server will save 2026-06-22 12:00 to the database.
If America/Sao_Paulo changes to -02:00 on 2027, it doesn’t affect conversions for past dates. You still get 2026-06-22 09:00 when trying to convert 2026-06-22 12:00 to local time in America/Sao_Paulo.
edit: [1] unless it was wrong. in which case, you actually still want the UTC timestamp stored, so that you can just update tzdata and get correct local times, as opposed to saving the wrong local times in your database, that you now have to also fix.
If you want to see if an employee is late compared to the time their shift starts, the system needs to know the time their shift starts in UTC, because otherwise if they start a shift in during a timezone change, it’ll think they’re extremely late/early.
If you want to pay them for their (clock out - clock in) time, UTC.
If they’re a remote worker on your team, and you want see your entire team’s availability, you should probably see it in _your_ local time instead.
I’ve found that whenever it looks like I need to know the local time that the user had when they did something, it’s because I’m implicitly anchoring it to some other timestamp that my system doesn’t know and that should also be recorded in UTC (like, in this example, the time their shift is supposed to start).
Some nuance applies, of course.
Sure, you can reconstruct it if you stored the "where" part somewhere else, and associated the "where" with the timestamp's timezone (e.g. the Offices table has a Timezone column that you JOIN with the punch-in times). But that assumes you stored it somewhere else. It also assumes said storage is readily available. It also assumes there was no human error in recording that value (what if the office was accidentally assigned Seattle PST instead of BC PST back when it didn't matter?) It also assumes it was possible to record it correctly in the first place (what if the office is in Kimberley, BC but the software only allowed selecting BC timezone?)
Alternatively, you store the timezone directly in the timestamp itself and avoid all these problems and more.
That is permanently fixed and can be losslessly and perfectly converted to the corresponding date in the past if you care about what local time was at that instant at some location.
The bottom line is, if wall time is important, past or present, wall time needs to be stored.
The only thing that can be guaranteed about a UTC timestamp is it's a UTC timestamp.
Most of my career I’ve seen problems where it’s out of date, never where it’s up to date and wrong.
Changes to past and future timestamps
Since 2022 Moldova has observed EU transition times, that is, it has sprung forward at 03:00, not 02:00, and has fallen back at 04:00, not 03:00. (Thanks to Heitor David Pinto.)For example, if tzdata is 1 hour off, and you store your timestamps in UTC, it's immediately obvious that a local time is wrong because users will see events that just happened as having happened 1 hour ago. Update tzdata, and now everything is right.
If you store the wall time, it _looks_ right, but fails if you attempt to compare/sort it with times in different timezones. To fix it, you need to actually modify the data in your database.
Like the time clock example: sure what you're describing works if the user is just pressing a button to clock in and the server stores a UTC timestamp in response to a POST request or whatever.
But it's very common to need to backfill time. So the user backfills with their own supplied timestamps, those stamps get converted to UTC, tzdata changes a few months later, and HR is now asking for an explanation as to why they were late for those backfills and how it's possible they were working an hour after the shop closed.
It's never as simple as "just store it in UTC".
Conversion to UTC is lossy, so I prefer to keep up with the user-supplied time where appropriate.