On Thursday, August 1, 2024, Chris BSomething <
xpusostomos@gmail.com> wrote:
.
Anyway, isn't the documentation wrong?
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.
