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