Subject: AT TIME ZONE correction

From: Bruce Momjian <[email-masked]>
Date: Sat, 1 Sep 2018 18:55:57 -0400

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.

-- 
  Bruce Momjian  <[email-masked]>        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 +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index bb794e0..ba22b7d
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*************** SELECT TIMESTAMP '2001-02-16 20:38:40' A
*** 8145,8153 ****
  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
  <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
  </screen>
!     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).
     </para>
  
     <para>
--- 8145,8154 ----
  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
  <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
  </screen>
!     The first example takes a time stamp without time zone,  converts it
!     to local time (PST, UTC-8), then  converts it to time in MST (UTC-7)
!     for display.  The second example takes a time stamp specified in EST
!     (UTC-5) and converts it to time in MST (UTC-7) for display.
     </para>
  
     <para>

---

From: Tom Lane <[email-masked]>
Date: Sat, 01 Sep 2018 19:30:43 -0400

> 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.

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

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 1 Sep 2018 19:37:36 -0400

> But you're not making it better.

Yes, I am still researching and realize my diff is wrong.  Let me keep
working and I will repost.

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 1 Sep 2018 23:28:21 -0400

> working and I will repost.

I have developed the attached patch, which I think is an improvement.

index bb794e0..53aa515
*************** SELECT date_trunc('year', TIMESTAMP '200
*** 8082,8089 ****
     </indexterm>
  
     <para>
!     The <literal>AT TIME ZONE</literal> construct allows conversions
!     of time stamps to different time zones.  <xref
      linkend="functions-datetime-zoneconvert-table"/> shows its
      variants.
     </para>
--- 8082,8089 ----
     </indexterm>
  
     <para>
!     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.
     </para>
*************** SELECT TIMESTAMP '2001-02-16 20:38:40' A
*** 8145,8153 ****
  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
  <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
  </screen>
!     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).
     </para>
  
     <para>
--- 8145,8159 ----
  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
  <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
  </screen>
!     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.)
     </para>
  
     <para>

---

From: Tom Lane <[email-masked]>
Date: Sun, 02 Sep 2018 14:21:58 -0400

>       linkend="functions-datetime-zoneconvert-table"/> shows its

Maybe it'd be more to the point to say that it allows conversion between
"timestamp with time zone" and "timestamp without 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).

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Sun, 2 Sep 2018 22:11:59 -0400

> and the specified zone (in a direction dependent on which type is

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

index bb794e0..5e3d54f
*** 8082,8091 ****
     </indexterm>
  
     <para>
!     The <literal>AT TIME ZONE</literal> construct allows conversions
!     of time stamps to different time zones.  <xref
!     linkend="functions-datetime-zoneconvert-table"/> shows its
!     variants.
     </para>
  
      <table id="functions-datetime-zoneconvert-table">
--- 8082,8092 ----
     </indexterm>
  
     <para>
!     The <literal>AT TIME ZONE</literal> converts time
!     stamp <emphasis>without time zone</emphasis> to/from
!     time stamp <emphasis>with time zone</emphasis>, and
!     <emphasis>time</emphasis> values to different time zones. <xref
!     linkend="functions-datetime-zoneconvert-table"/> shows its variants.
     </para>
  
      <table id="functions-datetime-zoneconvert-table">
  
     <para>
--- 8146,8158 ----
  SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
  <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
  </screen>
!     The first example adds a time zone to a value that lacks it.
!     The second example shifts the time stamp with time zone value
!     to the specified time zone, and returns the value without a
!     time zone.  This allows storage and display of values different
!     from the current <varname>TimeZone</varname> value.  Converting
!     <emphasis>time</emphasis> values to other time zones uses the currently
!     active time zone rules since no date is present.
     </para>
  
     <para>

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 3 Sep 2018 09:20:34 -0400

On Sun, Sep  2, 2018 at 10:11:59PM -0400, Bruce Momjian wrote:
> On Sun, Sep  2, 2018 at 02:21:58PM -0400, Tom Lane wrote:

I polished the text some more and changed the three-letter time zone
abbreviation (e.g., MST) to use the more general text, e.g.
"America/Denver".  We should not be encouraging people to specify the
daylight savings time status based on the date in the date/time string.

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index bb794e0..2135799 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8082,10 +8082,11 @@ SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
    </indexterm>
 
    <para>
-    The <literal>AT TIME ZONE</literal> construct allows conversions
-    of time stamps to different time zones.  <xref
-    linkend="functions-datetime-zoneconvert-table"/> shows its
-    variants.
+    The <literal>AT TIME ZONE</literal> converts time
+    stamp <emphasis>without time zone</emphasis> to/from
+    time stamp <emphasis>with time zone</emphasis>, and
+    <emphasis>time</emphasis> values to different time zones. <xref
+    linkend="functions-datetime-zoneconvert-table"/> shows its variants.
    </para>
 
     <table id="functions-datetime-zoneconvert-table">
@@ -8130,24 +8131,29 @@ SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
 
    <para>
     In these expressions, the desired time zone <replaceable>zone</replaceable> can be
-    specified either as a text string (e.g., <literal>'PST'</literal>)
+    specified either as a text string (e.g., <literal>'America/Los_Angeles'</literal>)
     or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
     In the text case, a time zone name can be specified in any of the ways
     described in <xref linkend="datatype-timezones"/>.
    </para>
 
    <para>
-    Examples (assuming the local time zone is <literal>PST8PDT</literal>):
+    Examples (assuming the local time zone is <literal>America/Los_Angeles</literal>):
 <screen>
-SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
+SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'America/Denver';
 <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 19:38:40-08</computeroutput>
 
-SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';
+SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'America/Denver';
 <lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
 </screen>
-    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).
+    The first example adds a time zone to a value that lacks it, and
+    displays the value using the current <varname>TimeZone</varname>
+    setting.  The second example shifts the time stamp with time zone
+    value to the specified time zone, and returns the value without a
+    time zone.  This allows storage and display of values different
+    from the current <varname>TimeZone</varname> setting.  Converting
+    <emphasis>time</emphasis> values to other time zones uses the currently
+    active time zone rules since no date is supplied.
    </para>
 
    <para>

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 4 Sep 2018 22:35:35 -0400

> daylight savings time status based on the date in the date/time string.

Patch applied through 9.3.  I ended up adding a third example:

	https://git.postgresql.org/pg/commitdiff/dd6073f22a6b5dd6181d8324465dd3c0bf1851e8