Future events: store the local (at the event) date and time and timezone. You’ll keep the right context even if lawmakers decide to switch things up. You want to see your doctor at 8:30 AM on Monday September 14, 2026 whether it’s daylight saving time, or standard time or “they” decide on a fractional hour offset between the time you set the appointment and the time you attend the appointment.

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.

Copying what I posted under the original[0] that no one noticed because it's quite relevant to your mention of UTC for past events:

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.

[0] https://news.ycombinator.com/item?id=48558005

[1] https://blog.nytsoi.net/2022/03/13/utc/

However, keep in mind that there is *no way* to store the time of a future event in a way that won't someday break unexpectedly. It just physically can't be done.

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.

Great advice! The really tricky part to me is when you have an event your recorded before it happened, but want to look it up after the event has passed (e.g., you want to look up the doctor's appointment a year after it occurred). The simplest and mostly solid answer I've been able to come up with is:

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.

What about virtual events between participants in different time zones? Whose do you keep stable if one has their clock moved under them?

> What format should you use? Human readable strings for longterm storage, because when things go wonky, it’s easier to debug.

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?".

UTC for past events doesn't always work either. For example, historical employee punch-in times.

UTC timestamps should only ever be used for points in time in the most literal sense, and nothing else.

[flagged]