Re: Re: Timestamp with vs without time zone.

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-pgsql@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.
> |_|_) |                    |
> | |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
> __/   | http://www.hjp.at/ |       challenge!"