A few decades later, timezones are changed. PST should add 30 minutes, all others stay the same. You only have 4102452000, what should you convert it into? You lost the time zone information, so you can't make the correct decision.
No. Say I have a database with a bunch of timestamps for future events. I calculate the time of those events with my current idea of local time (because my customer or because legal requirements require me to do something at a specific local time), convert to GMT, and store as a timestamp in my database.
In 2024, California decides to no longer observe Daylight Savings Time. All of those current timestamps no longer happen at the proper time in the newer version of America/Los_Angeles.
So there's a big pitfall here, in that the relation between localized time and UTC timestamp changes.
If you need to schedule future events it gets obviously a lot more complicated, you don't need a timestamp you need local time and you need it to be robust to future changes in timezones, DSTs, politics.
If you care about a past moment in time as reported by GPS or a computer clock-- use a UTC timestamp.
If you care about a future time delineated by a precise interval from now-- use a UTC timestamp.
If you care about a future time expected to be delineated in a specific time zone, use a timestamp in local time and note the zone.
If you care about a future time in a customer's time zone no matter where they may be, store the time in an unspecified local time and the customer for whom the time applies. Etc.
It takes a bit of housekeeping, but isn't especially difficult.
> if that actually happens
The IANA time zone database [1] is updated several times a year, it happens all the time
Thus reinventing zoned datetimes badly
> if that actually happens
It happens literally all the time, sometimes with very little heads up e.g. the decision to apply DST or not can be take mere weeks before it actually occurs[0], and something as impactful as an entire calendar day disappearing can happen with 6 months notice[1].
[0] https://egyptindependent.com/egypts-hasty-goodbye-daylight-s..., https://codeofmatt.com/time-zone-chaos-inevitable-in-egypt/
[1] https://www.theguardian.com/world/2011/dec/30/samoa-loses-da...
I do find that having the timestamp at hand makes math easier in a lot of the use cases I've come across. I wonder if there is an easy hybrid solution where you can capture timezone info, timestamp info and have it adjust without a full DB update, even if timezone offsets change. Maybe a mapping table of some kind, with versioned timezone codes.
For past events, you can store timestamps, it doesn't matter, the mapping is known and fixed.
Example: nobody cares if a concert planned on November 19, 2024 at 19:00 in Honolulu starts in 12345600 seconds or 12349200 seconds since now. But everyone cares that everone's calendars and watches are in sync by that time and show specifically that date and that time, regardless of how many times people switched DST or timezones in the years in-between.
That means when daylight savings time rules change or timezone lines are moved the UTC time of these events change.
Unfortunately I don't believe anyone has standardized a format for storing times which are specified local time at a specified location on a specified date. So it is all roll-your-own or use UTC or zoned datetime and be burned when things change.
From my experience the latter is safer because people tend to save datetime strings in a format that's either not standard nor unambiguous.
When we reimplemented the system from an ancient mainframe, we stored the value as a Unix timestamp and a standard representation of local time. It was derived from an old format going backwards and recorded in the new format going forward.
Having both is critical for our successors to figure out wtf is going on. UTC 1/1/70 is an anchor point, and the local representation is canonical at a point in time. I don’t recall how 1968-1970 was handled. Not only do you have to plan for “Berlin time” going away, but for daylight (or double daylight) times changing. Having both entries allows you to reconcile, so the poor bastard figuring out what to purge in 2090 has a fighting chance!