"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Tuesday, September 21, 2021, Peter J. Holzer <hjp-pgsql@hjp.at> wrote:
>> These two values are completely indistinguishable. That's good for a
>> timestamp (they are the same time after all). But they are not a
>> "timestamp with time zone". The time zone is not part of the value but
>> of the environment.
> No, it’s inherent to the data type itself. The data type has an implied
> time zone of UTC. That is a reasonable definition of “with” in my book.
The SQL standard is pretty clear that they intend "timestamp with time
zone" to include a time zone represented by a specific UTC offset.
We didn't implement it that way, which is not great, but with
twenty-some years of history there it seems unlikely that we'll change.
Especially since the SQL spec hasn't exactly covered itself with glory
in terms of providing *useful* datetime types. (Anybody ever found a
use for timetz?)
I think there is plenty of application for timestamps that actually
include (civil) time zones. Calendaring, for example. If I make an
appointment to see a friend at 2PM some months from now, it's
understood that that's in the local time zone; if some lawmakers take
it on themselves to fool with the DST rules before then, we're still
going to meet at 2PM local time. And it'd be useful to know whether
that now conflicts with appointments defined by reference to some
other zone, so the easy way of "assume it's all local time" doesn't
cut it.
This same example demonstrates why the SQL spec's idea of "timestamp
with time zone" doesn't cut it. You really need some DST-aware notion
of timezone identity. But, as already covered upthread, the can of
worms that that opens is so large that nobody has wanted to try to
tackle it as a primitive Postgres datatype.
regards, tom lane
