My point is that either you save a properly localized tz-aware time in unambiguous standard format (iso 8601?) or you save a universal timestamp (and location if needed) and defer all the localization process to the moment you need to display localized time.

From my experience the latter is safer because people tend to save datetime strings in a format that's either not standard nor unambiguous.

I don't think you're answering the question here though. It's completely plausible there are situations where the contant is local time, not UTC (or TAI, if you want to get really obnoxious). Let's say I made an appointment to get my hair done in Berlin 10 years in advance, at 14:00 local time 29 Dec 2031, for whatever reason. Let's say someone goes crazy and moves Berlin to +1:30 this decade. I would still expect to show up at 14:00 local, but that's not the same universal time it was 10 years ago. How do you represent that in your DB where everything is in UTC?

Frankly, time doesn't work that way... if i am atm (CET) and i make an appointment for sometime in summer 2022 (CEST), then i don't expect the hair salon to make the appointment in CET.

No, I think we agree. We're both making the appointment at whatever everyone in Berlin calls 14:00 on the day of the appointment.

Honestly I'm not sure, in ten years Berlin timezone could not exist anymore, in your example you need a special time representation that says "this time in local time, whatever local time will mean in the future". I'm not sure we have that in current date time representation standards, do we? you could still save tz-unaware local time and a location, and figure out in the future what local time means at that point of time and space.

I’ve worked on systems where data has a 100-126 year retention objective. We had the “fun” of dealing with this looking backward as far as 1968!

When we reimplemented the system from an ancient mainframe, we stored the value as a Unix timestamp and a standard representation of local time. It was derived from an old format going backwards and recorded in the new format going forward.

Having both is critical for our successors to figure out wtf is going on. UTC 1/1/70 is an anchor point, and the local representation is canonical at a point in time. I don’t recall how 1968-1970 was handled. Not only do you have to plan for “Berlin time” going away, but for daylight (or double daylight) times changing. Having both entries allows you to reconcile, so the poor bastard figuring out what to purge in 2090 has a fighting chance!