Re: AT TIME ZONE correction

Bruce Momjian <bruce@momjian.us> writes:
> Looking over the AT TIME ZONE docs, I think they are subtly confusing. 
> The order of conversion specific in the first example should _start_
> with the assumption of local time zone for the time stamp, not something
> that happens after AT TIME ZONE is applied.  The ordering in current
> docs makes the second example confusing too.

> The attached patch fixes this.

I think it's you that are confused.  The text as written is correct,
or at least arguably so; your revision is definitely incorrect.

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

The way I'd describe this is that we start with a timestamp,
which has no particular timezone.  The AT TIME ZONE says "Assume that
this zoneless timestamp is in MST, and convert it to timestamp with
time zone (which will be in UTC, internally)".  Then after that, the
UTC timestamptz value is converted to PST8PDT for display purposes,
but that's done by timestamptz_out not AT TIME ZONE.

If we were going from PST to MST as your patch describes it, the
output would be one hour later not one hour earlier than the input.

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';

Here we've got a time value that was initially given in EST (-05),
but was converted to UTC by timestampz_in.  Then the AT TIME ZONE
says "Please convert this UTC value to MST, and emit it as a zoneless
timestamp" (which will not be subject to any further conversion when
it's displayed).

The existing text is indeed a bit deficient, because it fails to
draw a clear boundary between what the AT TIME ZONE operator is
doing and what is being done by the timestamp(tz) I/O functions.
But you're not making it better.

            regards, tom lane