Re: Timestamp with vs without time zone. - Mailing list pgsql-general

On Sep 21, 2021, at 12:34 PM, Dave Cramer <davecramer@postgres.rocks> wrote:

On Tue, 21 Sept 2021 at 13:20, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

On 2021-09-21 20:50:44 +1200, Tim Uckun wrote:
> 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.

Yes, but HOW IS THAT TIME ZONE STORED? 

As a user you can say "I don't care, just make it work somehow".

But as a developer you have to decide on a specific way. And as a
database developer in particular you would have to choose a way which
works for almost everybody.

And that's the problem because ...

> 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

... people have different needs and it would be difficult to satisfy
them all.

Simply storing an offset from UTC is simple, fast, doesn't take much
space - but it would be almost as misleading as the current state. A
simple offset is not a time zone.

Storing the IANA timezone names (e.g. 'Europe/Vienna') would store an
identifier for what most people think of as a time zone - but that takes
a lot of space, it needs a lookup for almost any operation and worst of
all, you couldn't index such a column (at least not with a btree index)
because the comparison functions aren't stable.

You could use a numeric indentifier instead of the name, that would take
less space but wouldn't solve the other problems (and add the problem
that now you have just added another mapping which you need to maintain).

There are other ways, but I'm sure they all have some pros and some
cons. None will be perfect.

So I don't think there is an obvious (or even non-obvious, but clearly
good) way for the PostgreSQL developers to add a real "timestamp with
timezone" type.

As an application developer however, you can define a compound type (or
just use two or three columns together) which satisfies the needs of
your specific application.

> It's just that the phrase "timestamp with time zone" would seem to
> indicate the time zone is stored somewhere in there.

I absolutely agree. Calling a type which doesn't include a timezone
"timestamp with timezone" is - how do I put this? - more than just
weird. "timestamp without timezone" should be called "local timestamp
with unspecified timezone" and "timestamp with timezone" should be
called "global timestamp without timezone". However, those aren't SQL
names.

I would say this is a perspective thing. It's a timestamp with a time zone from the client's perspective.