[Ndymium]

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/

[jagged-chisel]

But if your user does jog from 2:00am to 3:30am, your localized data is going to look wrong in the fall in a timezone where the clock changes from daylight saving and the 2:00am hour repeats itself.

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 … )

[Ndymium]

If my user does jog at 2 AM, then it's better to show 2 AM than 7 PM or 10 AM even if there is a slight chance that on one singular day 2 AM might repeat itself. The user who is aware of how daylight savings works will surely be able to figure that out.

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.