Re: Inconsistency of timezones in postgresql

On Thursday, August 1, 2024, Chris BSomething <xpusostomos(at)gmail(dot)com> wrote:
.
>
>
> Anyway, isn't the documentation wrong?
>
> https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-
> DATETIME-ZONECONVERT
>
> timestamp without time zone AT TIME ZONE zone → timestamp with time zone
> Converts given time stamp without time zone to time stamp with time zone,
> assuming the given value is in the named time zone.
> timestamp '2001-02-16 20:38:40' at time zone 'America/Denver' → 2001-02-17
> 03:38:40+00
>
> Unfortunately the doco doesn't tell us what environmental time zone it is
> assuming so the user is not enlightened by the examples.
>

Ok, better wording - but a bit long and the further examples on the page
say much the same thing…

Attaches time zone “zone” to the input timestamp value to create a
point-in-time timestamptz value. In the following example, the output is
20:38 Denver time. This same point-in-time, in UTC, is 03:38 the following
day. Since timestamptz values are printed in the session Time Zone,
defined to be UTC for these examples, this 03:38 time is what you see
printed to the screen. Its microsecond epoch value is also what is stored
as the internal representation.

This last part might be a key point of confusion. Time is stored as
microseconds since an epoch in UTC. But this is just an internal
representation that has no bearing on the semantics of how the types
operate in practice. So yes, when a timestamp without time zone is stored
the epoch delta is computed as if that timestamp were in UTC. But in
practice that value has no time zone information associated with it at
all. You may use AT TIME ZONE to attach a time zone to the value. And the
only useful answer to how to go from “time zone null” to “time zone Denver”
is to just say the input time is the time in Denver.

David J.