Re: AT TIME ZONE correction

fOn Sun, Sep 2, 2018 at 02:21:58PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > ! The <literal>AT TIME ZONE</literal> construct allows the addition,
> > ! conversion, and removal of time zones for time stamp values. <xref
> > linkend="functions-datetime-zoneconvert-table"/> shows its
> > variants.
>
> Maybe it'd be more to the point to say that it allows conversion between
> "timestamp with time zone" and "timestamp without time zone".
>
> > ! The first example takes a time stamp without time zone and interprets
> > ! it in the MST time zone (UTC-7), returning a time stamp with time
> > ! zone value which is displayed in local time (PST, UTC-8). The second
> > ! example takes a time stamp with time zone value (EST, UTC-5) and
> > ! converts it to the date and time in MST (UTC-7) without time zone.
> > ! Basically, the first example takes the date and time and puts it in
> > ! the specified time zone. The second example takes a time stamp with
> > ! time zone and shifts it to the specified time zone. (No time zone
> > ! designation is returned.)
>
> I still find this to be more confusing than helpful. In particular,
> I do not think that it's possible to explain this behavior clearly
> without mentioning that timestamp with time zone values are always
> stored in UTC and what AT TIME ZONE really does is convert between UTC
> and the specified zone (in a direction dependent on which type is
> supplied as argument).

Agreed. The more I dig into this the more I learn. I have developed
the attached patch which I hope this time is an improvement.

Is there any value to showing these two queries which show how calling
AT TIME ZONE twice cancels itself out:

SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'America/Chicago' AT TIME ZONE 'America/Chicago';
timezone
---------------------
2018-09-02 07:09:19

SELECT '2018-09-02 07:09:19-04'::timestamptz AT TIME ZONE 'America/Chicago' AT TIME ZONE 'America/Chicago';
timezone
------------------------
2018-09-02 07:09:19-04

or this one which shows how to convert a date/time from one time zone to
another:

SELECT '2018-09-02 07:09:19'::timestamp AT TIME ZONE 'Asia/Tokyo' AT
TIME ZONE 'America/Chicago';
timezone
---------------------
2018-09-01 17:09:19

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +