That's all true and I won't argue about the madness that is timezones in the world. I am simply thinking it would be some sort of a struct like thing which would store the numerical value of the time stamp and also the time zone that time was recorded in. Presumably everything else is an insane calculation from there. What was the offset on that day? I guess it depends on the daylight savings time. What would the conversion to another time zone be? That would depend on the DST settings on that day in both places.
Mankind can't agree on what side of the road to drive on, what the electrical voltage should be at the wall, what those plugs should be, how you should charge your phone or anything else for that matter so there is no way of avoiding the insanity. It's just that the phrase "timestamp with time zone" would seem to indicate the time zone is stored somewhere in there. On Tue, Sep 21, 2021 at 8:44 PM Peter J. Holzer <hjp-pg...@hjp.at> wrote: > > On 2021-09-21 09:39:59 +0200, Karsten Hilbert wrote: > > > It seems like it would be so much more useful if the timestamp with > > > time zone type actually stored the time zone in the record. > > > > Which one ? > > To expand on that question a bit: > > There are several formats to specify a time zone: By offset, by name > (several nomenclatures), etc. > > For example, Karsten's mail had a timestamp of "2021-09-21 09:39:59 > +0200". Thst's enough information to convert it to UTC, but not enough > for date arithmetic. For example what is > '2021-09-21 09:39:59 +0200' + '2 months'::interval? > > Should the result be '2021-11-21 09:39:59 +0200' or '2021-11-21 09:39:59 > +0100'? I'm guessing that Karsten is in Germany, so it's probably the > latter. But to compute that you need to know that the timezone is > Europe/Berlin (or at least CET). Even that is not enough for dates in > the more distant future. The EU has decided to abolish DST (that should > have happened in 2020, but of course there was that little problem that > got in the way), but we don't know when that will happen and which > timezone Germany will choose. So for a date in e.g. 2025 we simply don't > know what the timezone offset will be. > > hp > > -- > _ | Peter J. Holzer | Story must make more sense than reality. > |_|_) | | > | | | h...@hjp.at | -- Charles Stross, "Creative writing > __/ | http://www.hjp.at/ | challenge!"