https://www.postgresql.org/docs/current/static/datatype-date...
(Edit: or not. See child comment)
Better example: Typically an events schedule should specify destination timezone when registering an event, so that if regulations on local timezone change, scheduled event times remain valid.
But in this case, there's a lot of other information you want to store: first date in the series, repetition frequency, time of the appointment, location (e.g. "Europe/London" as opposed to "GMT" or "UTC+0" which would not be adequate). Basically, you're not so much storing a series of dates as storing a thunk for generating dates.
I'm sure a use case exists where you want to store a timezone, but I don't know what it is. I try and use UTC everywhere, and only worry about time zones when displaying.
Which leads me to my rule with time programming: Never fail to use a solid library, unless you're unfortunate enough to be writing one.
This[4] is a great, necessary but not sufficient book if you have to do that.
[1] https://en.wikipedia.org/wiki/International_Earth_Rotation_a...
[2] For example, https://en.wikipedia.org/wiki/Swedish_calendar
[3] https://en.wikipedia.org/wiki/Easter#Computations
[4] https://www.amazon.com/Calendrical-Calculations-Nachum-Dersh...
If you schedule a meeting for 4pm Tuesday next week, but DST happens Friday this week, you still want the meeting on 4pm Tuesday next week. The local timezone and local timestamp is necessary for that.
If you only store the timezone with a UTC timestamp, when you convert back after DST shifts you'll end up with 3pm or 5pm (depending on the shift), not the expected 4pm for the actual meeting. UTC in this case is not the anchor, the local timestamp the user set for the meeting is.
The local TZ => UTC formula may change for any reason, whether it's daylight saving time or any other random situation. It might be relatively stable in some locations (although it can change even within a global timezone like EST based on location) but there are constant updates being made. You can look at the IANA and Microsoft timezone database updates yourself. Why create risk when not necessary?
I'm not sure what this means. Either way, to store an appointment for a certain user, you need the local timezone of that user and the local timestamp. UTC version of that timestamp is optional (maybe for easier calculations in the database), but you must have the local timestamp to convert correctly.
Local TZ => UTC is a formula that is not constant. You cannot store the output of this formula and expect to reverse and get back the original input precisely because of the changing formula.
The timezone that the user will experience on that date.
>Either way, to store an appointment for a certain user, you need the local timezone of that user and the local timestamp.
Correct. I did not disagree with this.
>Local TZ -> UTC is a formula that is not constant. You cannot store the output of this formula and expect to reverse and get back the original input precisely because of the changing formula.
Also correct. But as I said, your original comment implied this is a concern for all DST events which is not true. It is only a concern when the time was converted using some DST assumption and then that assumption is invalidated (eg the software decided to convert "4pm Tuesday local" to UTC assuming the timezone on Tuesday will be UTC+7, but the DST change happens to come early this year and in fact it's UTC+8 on Tuesday).
Regardless, I agree that taking this risk is not correct. Such a time should not be converted to UTC to begin with. Better to store the original local time.
Timezones, especially globally, have enough updates being made that they are not something worth the risk just to save a few bytes.
Eastern Time Zone is a single time zone, that has different offsets depending on season, formally referred to as EDT and EST to make it easier to identify as daylight or standard references to UTC.
Even regions within the same time zone don't follow the standard exactly, so Panama does not observe daylight saving time while New York does. This is why we have even more granular settings used for calendars and dates.
You either just store UTC and co, or you store it just as an datetime + offset.
100% of apps in production don't handle most of the timezone intricacies anyway and the sky hasn't fallen (heck, the sky hasn't even fallen for Y2K).
100% of apps? You must not know what timezone intricacies are then or just how much effort is spent to make sure time itself is properly handled, especially in any major application that has global users.
You'd be surprised.