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.

▲ drdexebtjl 7 hours ago | parent | next [-]

Why not? It sounds like it would be correct even if the employee has a shift that includes a offset change.

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

▲

Xirdus 6 hours ago | parent [-]

Correct according to what? An employee who punched in at 9AM wouldn't show up as having punched in at 9AM anymore. Not unless you also store the exact timezone the UTC timestamps have been created with - but that's basically local timestamps with extra steps.

▲

drdexebtjl 5 hours ago | parent [-]

tzdata doesn’t change retroactively [1].

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.

▲

Xirdus 3 hours ago | parent [-]

But which tzdata? Do you have the timezone or do you not have the timezone? If you have the timezone then why is your timestamp in UTC and not in the timezone that you have to store alongside the UTC timestamp?

	▲	drdexebtjl 2 hours ago \| parent [-]
		Whichever timezone is relevant to the analysis later when the data is read. 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.

▲ Terr_ 7 hours ago | parent | prev [-]

If past timestamps (UTC or otherwise) are unreliable, then there is some kind of math-bug going on.

▲ dqv 6 hours ago | parent | next [-]

Not always a math bug. Sometimes a human bug. Tzdata can have errors (it's crowdsourced after all) that cause past UTC stamps to be incorrect because that incorrect tzdata was used at conversion time. And since most people aren't storing the tzdata version they're using with the stamp, it would be very difficult to make corrections without also corrupting other stamps.

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.

▲ drdexebtjl 6 hours ago | parent | next [-]

When was the last time tzdata was wrong about a period that already passed?

Most of my career I’ve seen problems where it’s out of date, never where it’s up to date and wrong.

▲ dqv 6 hours ago | parent [-]

For the 2026a release:

    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.)

▲

drdexebtjl 5 hours ago | parent [-]

Interesting. I could see that as an argument also for storing it in UTC, no?

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.

	▲	dqv 4 hours ago \| parent [-]
		Only for server-supplied timestamps. 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.

▲ __s 6 hours ago | parent | prev [-]

Seems like for airtightness you'd store utc alongside utc of when timestamp was stored alongside timezone

▲ Xirdus 6 hours ago | parent | prev [-]

"UTC or otherwise" is important. Are you storing otherwise, or are you storing UTC? There are times where storing otherwise will lead to data loss in case the law changes whereas storing UTC would work (when you care about a literal point in time, like access logs). And there are times where storing UTC will lead to data loss in case the law changes whereas storing otherwise would work (when you care about wall clock time, like punch-in times).