Just store the time stamp and time zone separately and update the time stamp to reflect any changes to a time zone's offset (if that actually happens)...
It takes a bit of housekeeping, but isn't especially difficult.
> Just store the time stamp and time zone separately and update the time stamp to reflect any changes to a time zone's offset
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].
Thanks for this - I've learned something new today. I did not realise timezone updates were so frequent.
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.
As far as i'm aware, you should only store the location next to the timestamp and let the IANA[1] do the maintenance of the tz database. Please avoid the housekeeping on your side.
It actually is especially difficult at scale for non-trivial applications. Large databases won't be able to atomically change every instance which may lead to very frustrating bugs where collisions cannot be allowed. For example, on a calendar app. I agree with dtech, if you're storing in the future don't use a timestamp unless the input value is specifically intended to be timezone unaware, which is typically only useful for short term things like control systems or alarms. For longterm human uses a datetime+timezone field or an ISO datetime+timezone string is safer.
It takes a bit of housekeeping, but isn't especially difficult.