Re: AT TIME ZONE correction - Mailing list pgsql-docs

fOn Sun, Sep  2, 2018 at 02:21:58PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.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@momjian.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 +