Past events: UTC timestamp.
What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug.
Note: nothing stops you from optimizing for queries by adding a field to store (or using a calculated index for) the integer epoch offset (e.g. unix timestamps), just make sure you know which field is authoritative.
UTC timestamps should only ever be used for points in time in the most literal sense, and nothing else.
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
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.
Your approach assumes that we know what timezone the doctor's office will be in when the event happens. However, unless you know the exact lat/lon of that office — and maybe not even then — that's not something you can rely on.
Countries sometimes split themselves up. Provinces get annexed. Border towns may end up on the other side due to a treaty, even in times of peace. Multi-timezone countries may change which parts belong to which timezone. A town may get occupied, and the answer to the question of "what time is it" may depend on the loyalties of the person you ask.
Unless the doctor's office is physically located in Berlin, Germany, there is no guarantee that europe/berlin will always be its correct timezone. Even then, you may get the east/west Berlin split and one side deciding to abandon DST.
When an event happened in the past, we know exactly when it happened, and we can express that timestamp as "number of seconds after some reference point." When an event is planned for the future, we usually plan it for a specific hh:mm in a specific location, but we don't know when that is actually going to be.
Should we all add precise GPS coordinates to our salon appointments in case that neighborhood is seized by commandos from Newfoundland who really really want us all on GMT? I’m personally not sure it’s worth the effort.
If you're feeling mean, randomize whose time remains stable (to make it hard to predict), move the meeting for the organizer, pick the time that maximizes the number of participants who will have the meeting time change, or split the difference and move everyone. Meeting was at 10 AM for Alice and 9 AM for Bob, but now it can be at either 11 AM for Alice and 9 AM for Bob or 10 AM for Alice and 8 AM for Bob? Now the meeting is at 10:30 AM for Alice and 8:30 AM for Bob.
Daylight savings time changes, can't be globally banned fast enough really.
The naming of "timestamp with time zone" is one of my favorite pet peeves. It's one of those things that you can say "well technically it's true" about.
The article suggests that for past events, UTC and this timestamptz would be acceptable as a general rule, but even there it depends on what you will be doing with the data. If you intend to interpret it as a series of local occurrences and try to visualize/summarize that data later, you may be in for a surprise as your user has moved to another timezone and now all the past events are translated to the wrong local hours [1]. For example, your system might end up showing that the user's best time for jogging based on historical data is at 2 in the night.
It’s easier to convert historical records from UTC because the official rules don’t change for past dates (there may certainly be data errors in tzdata … what’re gonna do … )
Technically you might argue that I should show 1 AM if the user did run at 2 AM in summer time, but everyone I personally know keeps their schedule over DST transitions. That is, if they did something at X o'clock before the transition, they'll keep doing it at X o'clock after it (sleep be damned). So generally showing 2 AM would be the most correct solution.
You cannot get that information from just UTC if you don't know where the user was when they made those historical events. Thus you either have to keep a history of their location (complicated) or just store the local timestamps (or at least the offsets) at the time of event. Always being able to convert from UTC with no extra data assumes that the user will never move, which might be fine if your application is limited to users in a single country.
1. If you want to know when something happened and a particular place is important (like the previously mentioned doctor's appointment), store the local date/time with timezone data. That covers you in case the timezone changes before your recorded event happens. Personally, I would not store reflexively store dates/times in a string. For the cases I encounter, that feels like primitive obsession since you can always use EXTRACT in a query to simplify output.
2. If will you need to lookup the date and time after an event occurred, write a separate field that includes timezone offset field (e.g. -1, +1, -8, etc.) in case you want to look up exactly when an event previously happened. This (mostly) covers you from timezone shifts that occur at that particular location between when you wrote the data vs some later date. This falls apart if the timezone you're converting to also changed their timezone between now and the event. Also, if your timezone shifts between when you wrote the record and the actual event.
I wonder if someone has a temporal record of timezone shifts. You could solve a lot of edge cases with something like that. Then you could write a query that asks for the timezone's offset as of a specific date. That would make life much easier. Then you could skip the timezone offset field I mentioned in #2.
Your appointment time is no longer the important item once it's in the past. It's probably easier to just get a list of your visits from the last year, and select the one you want to dig into. Not listed? Expand the range to 13 months. I doubt a situation exists where it matters that one must query the exact date and time in order to find that appointment.
DateTime logic in general is just difficult in general.. Most systems can translate to/from UTC with ease, it's just when the localized offset of the futre changed like in this case.
You can just use a TIMESTAMP with no TZ data. It's functionally the same as using the string but simpler because you avoid all the string handling headaches and gain the benefit of avoiding to avoid double booking and date/time functions to answer questions like "how many appointments do I have in April?".
See Current: https://www.postgresql.org/docs/current/datatype-datetime.ht...
And 7.1 (2001-04-13): https://www.postgresql.org/docs/7.1/datatype-datetime.html (Section 3.4.2)
