Обсуждение: Question about "AT TIME ZONE"

The following is taken from section 9.9.3 of the help docs
===========================================
Examples (supposing that the local time zone is PST8PDT):

SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
Result: 2001-02-16 19:38:40-08

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
Result: 2001-02-16 18:38:40
The first example takes a time stamp without time zone and interprets
it as MST time (UTC-7), which is then converted to PST (UTC-8) for
display. The second example takes a time stamp specified in EST
(UTC-5) and converts it to local time in MST (UTC-7).
===========================================

In the first example it says it is converted to PST "for display".  In
the second example it is not converted to PST for display.  Does this
mean that if a timestamp *with* a timezone is specified, and it also
includes "AT TIME ZONE", that it is not converted to PST "for display"
at the end?

I just want to make sure that these two examples perform completely
different tasks.  Essentially the first item:
SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
And this:
SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-07' AT TIME ZONE 'PST';
are the exact same thing.

Kind of confusing.

Regards,
Collin

"Collin Peters" <cadiolis@gmail.com> writes:
> In the first example it says it is converted to PST "for display".  In
> the second example it is not converted to PST for display.  Does this
> mean that if a timestamp *with* a timezone is specified, and it also
> includes "AT TIME ZONE", that it is not converted to PST "for display"
> at the end?

AT TIME ZONE does two different things that are sort of inverses of each
other: it can convert timestamp without timezone to timestamp with
timezone, or the reverse.  In the case where you're converting to ts
with tz, there's an additional step involved, which is converting the
UTC-base ts-with-tz value to your current TimeZone for display.  That
happens when SELECT emits the value as text, not as part of AT TIME ZONE.

AT TIME ZONE itself basically either adds or subtracts the UTC offset of
the named time zone, thus converting a value in UTC to or from a value
that's relative to the named zone.
        regards, tom lane