Fix breakage in new-in-7.3 timetz_zone() function: was giving random

results due to doing arithmetic on uninitialized values.  Add some
documentation about the AT TIME ZONE construct.  Update some other
date/time documentation that seemed out of date for 7.3.

2 files changed, 184 insertions, 43 deletions

diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml
index 1c07a9c5f16..cac65623dc5 100644
--- a/doc/src/sgml/datatype.sgml
+++ b/doc/src/sgml/datatype.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.108 2002/11/15 03:11:15 momjian Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/datatype.sgml,v 1.109 2002/11/21 23:31:20 tgl Exp $
 -->
 
  <chapter id="datatype">
@@ -1570,18 +1570,30 @@ SELECT b, char_length(b) FROM test2;
     </indexterm>
 
     <indexterm>
+     <primary>timestamp with time zone</primary>
+     <secondary>data type</secondary>
+    </indexterm>
+
+    <indexterm>
      <primary>timestamp without time zone</primary>
      <secondary>data type</secondary>
     </indexterm>
 
      <para>
-      Time stamp types exist as <type>timestamp [
-      (<replaceable>p</replaceable>) ]</type>, <type>timestamp [
+      The time stamp types are <type>timestamp [
       (<replaceable>p</replaceable>) ] without time zone</type> and
-      <type>timestamp [ (<replaceable>p</replaceable>) ] without time
-      zone</type>.  A plain <type>timestamp</type> is equivalent to
-      <type>timestamp without timezone</type>.
+      <type>timestamp [ (<replaceable>p</replaceable>) ] with time
+      zone</type>.  Writing just <type>timestamp</type> is equivalent to
+      <type>timestamp without time zone</type>.
+     </para>
+
+    <note>
+     <para>
+      Prior to <productname>PostgreSQL</productname> 7.3, writing just
+      <type>timestamp</type> was equivalent to <type>timestamp with time
+      zone</type>.  This was changed for SQL spec compliance.
      </para>
+    </note>
 
      <para>
       Valid input for the time stamp types consists of a concatenation
@@ -1615,11 +1627,38 @@ January 8 04:05:06 1999 PST
 
      <para>
       For <type>timestamp without time zone</type>, any explicit time
-      zone specified in the input is silently swallowed. That is, the
+      zone specified in the input is silently ignored. That is, the
       resulting date/time value is derived from the explicit date/time
       fields in the input value, and is not adjusted for time zone.
      </para>
 
+     <para>
+      For <type>timestamp with time zone</type>, the internally stored
+      value is always in UTC (GMT).  An input value that has an explicit
+      time zone specified is converted to UTC using the appropriate offset
+      for that time zone.  If no time zone is stated in the input string,
+      then it is assumed to be in the time zone indicated by the system's
+      <varname>TimeZone</> parameter, and is converted to UTC using the
+      offset for the <varname>TimeZone</> zone.
+     </para>
+
+     <para>
+      When a <type>timestamp with time
+      zone</type> value is output, it is always converted from UTC to the
+      current <varname>TimeZone</> zone, and displayed as local time in that
+      zone.  To see the time in another time zone, either change
+      <varname>TimeZone</> or use the <literal>AT TIME ZONE</> construct
+      (see <xref linkend="functions-datetime-zoneconvert">).
+     </para>
+
+     <para>
+      Conversions between <type>timestamp without time zone</type> and
+      <type>timestamp with time zone</type> normally assume that the
+      <type>timestamp without time zone</type> value should be taken or given
+      as <varname>TimeZone</> local time.  A different zone reference can
+      be specified for the conversion using <literal>AT TIME ZONE</>.
+     </para>
+
       <table tocentry="1" id="datatype-timezone-table">
        <title>Time Zone Input</title>
        <tgroup cols="2">
@@ -1707,24 +1746,28 @@ January 8 04:05:06 1999 PST
      <para>
       The following <acronym>SQL</acronym>-compatible functions can be
       used as date or time
-      input for the corresponding data type: <literal>CURRENT_DATE</literal>,
+      values for the corresponding data type: <literal>CURRENT_DATE</literal>,
       <literal>CURRENT_TIME</literal>,
       <literal>CURRENT_TIMESTAMP</literal>. The latter two accept an
-      optional precision specification.  (See also <xref linkend="functions-datetime">.)
+      optional precision specification.  (See also <xref linkend="functions-datetime-current">.)
      </para>
 
      <para>
       <productname>PostgreSQL</productname> also supports several
-      special constants for convenience, shown in <xref
-      linkend="datatype-datetime-special-table">.
+      special date/time input values for convenience, as shown in <xref
+      linkend="datatype-datetime-special-table">.  The values
+      <literal>infinity</literal> and <literal>-infinity</literal>
+      are specially represented inside the system and will be displayed
+      the same way; but the others are simply notational shorthands
+      that will be converted to ordinary date/time values when read.
      </para>
 
       <table id="datatype-datetime-special-table">
-       <title>Special Date/Time Constants</title>
+       <title>Special Date/Time Inputs</title>
        <tgroup cols="2">
 	<thead>
 	 <row>
-	  <entry>Constant</entry>
+	  <entry>Input string</entry>
 	  <entry>Description</entry>
 	 </row>
 	</thead>
@@ -1735,15 +1778,13 @@ January 8 04:05:06 1999 PST
 	 </row>
 	 <row>
 	  <entry><literal>infinity</literal></entry>
-	  <entry>later than other valid times</entry>
+	  <entry>later than all other timestamps (not available for
+	  type <type>date</>)</entry>
 	 </row>
 	 <row>
 	  <entry><literal>-infinity</literal></entry>
-	  <entry>earlier than other valid times</entry>
-	 </row>
-	 <row>
-	  <entry><literal>invalid</literal></entry>
-	  <entry>illegal entry</entry>
+	  <entry>earlier than all other timestamps (not available for
+	  type <type>date</>)</entry>
 	 </row>
 	 <row>
 	  <entry><literal>now</literal></entry>
@@ -1962,13 +2003,21 @@ January 8 04:05:06 1999 PST
     </para>
 
     <para>
-     There are several ways to affect the time-zone behavior:
+     There are several ways to select the time zone used by the server:
 
      <itemizedlist>
       <listitem>
        <para>
 	The <envar>TZ</envar> environment variable on the server host
-	is used by the server as the default time zone.
+	is used by the server as the default time zone, if no other is
+	specified.
+       </para>
+      </listitem>
+
+      <listitem>
+       <para>
+	The <varname>timezone</varname> configuration parameter can be
+	set in <filename>postgresql.conf</>.
        </para>
       </listitem>
 
@@ -1987,18 +2036,6 @@ January 8 04:05:06 1999 PST
 	sets the time zone for the session.
        </para>
       </listitem>
-
-      <listitem>
-       <para>
-	The construct
-<programlisting>
-<replaceable>timestamp</replaceable> AT TIME ZONE '<replaceable>zone</replaceable>'
-</programlisting>
-	where <replaceable>zone</replaceable> can be specified as a
-	text time zone (e.g., <literal>'PST'</literal>) or as an
-	interval (e.g., <literal>INTERVAL '-08:00'</literal>).
-       </para>
-      </listitem>
      </itemizedlist>
     </para>
 
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 71741948457..7443f001ef4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.130 2002/11/11 20:14:02 petere Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/func.sgml,v 1.131 2002/11/21 23:31:20 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -3549,9 +3549,14 @@ SUBSTRING('foobar' FROM 'o(.)b')   <lineannotation>o</lineannotation>
    <literal>*</literal>, etc.).  For formatting functions, refer to
    <xref linkend="functions-formatting">.  You should be familiar with
    the background information on date/time data types (see <xref
-   linkend="datatype-datetime">).  The date/time operators described
-   below behave similarly for types involving time zones as well as
-   those without.
+   linkend="datatype-datetime">).
+  </para>
+
+  <para>
+   All the functions and operators described below that take time or timestamp
+   inputs actually come in two variants: one that takes time or timestamp
+   with time zone, and one that takes time or timestamp without time zone.
+   For brevity, these variants are not shown separately.
   </para>
 
     <table id="operators-datetime-table">
@@ -3771,7 +3776,7 @@ SUBSTRING('foobar' FROM 'o(.)b')   <lineannotation>o</lineannotation>
 
        <row>
 	<entry><function>now</function>()</entry>
-	<entry><type>timestamp</type></entry>
+	<entry><type>timestamp with time zone</type></entry>
 	<entry>Current date and time (equivalent to
 	 <function>current_timestamp</function>); see <xref
                                                      linkend="functions-datetime-current">
@@ -3898,8 +3903,8 @@ SELECT EXTRACT(DOY FROM TIMESTAMP '2001-02-16 20:38:40');
       <listitem>
        <para>
         For <type>date</type> and <type>timestamp</type> values, the
-        number of seconds since 1970-01-01 00:00:00-00 (Result may be
-        negative.); for <type>interval</type> values, the total number
+        number of seconds since 1970-01-01 00:00:00-00 (can be negative);
+	for <type>interval</type> values, the total number
         of seconds in the interval
        </para>
 
@@ -4122,12 +4127,12 @@ SELECT EXTRACT(YEAR FROM TIMESTAMP '2001-02-16 20:38:40');
    <para>
     The <function>date_part</function> function is modeled on the traditional
     <productname>Ingres</productname> equivalent to the
-    <acronym>SQL</acronym>-function <function>extract</function>:
+    <acronym>SQL</acronym>-standard function <function>extract</function>:
 <synopsis>
 date_part('<replaceable>field</replaceable>', <replaceable>source</replaceable>)
 </synopsis>
-    Note that here the <replaceable>field</replaceable> value needs to
-    be a string.  The valid field values for
+    Note that here the <replaceable>field</replaceable> parameter needs to
+    be a string value, not a name.  The valid field values for
     <function>date_part</function> are the same as for
     <function>extract</function>.
    </para>
@@ -4192,6 +4197,95 @@ SELECT date_trunc('year', TIMESTAMP '2001-02-16 20:38:40');
    </para>
   </sect2>
 
+  <sect2 id="functions-datetime-zoneconvert">
+   <title><function>AT TIME ZONE</function></title>
+
+   <indexterm>
+    <primary>timezone</primary>
+    <secondary>conversion</secondary>
+   </indexterm>
+
+   <para>
+    The <function>AT TIME ZONE</function> construct allows conversions
+    of timestamps to different timezones.
+   </para>
+
+    <table id="functions-datetime-zoneconvert-table">
+     <title>AT TIME ZONE Variants</title>
+     <tgroup cols="3">
+      <thead>
+       <row>
+	<entry>Expression</entry>
+	<entry>Returns</entry>
+	<entry>Description</entry>
+       </row>
+      </thead>
+
+      <tbody>
+
+       <row>
+	<entry>
+	 <type>timestamp without time zone</type>
+	 <literal>AT TIME ZONE</literal>
+	 <replaceable>zone</>
+	</entry>
+	<entry><type>timestamp with time zone</type></entry>
+	<entry>Convert local time in given timezone to UTC</entry>
+       </row>
+
+       <row>
+	<entry>
+	 <type>timestamp with time zone</type>
+	 <literal>AT TIME ZONE</literal>
+	 <replaceable>zone</>
+	</entry>
+	<entry><type>timestamp without time zone</type></entry>
+	<entry>Convert UTC to local time in given timezone</entry>
+       </row>
+
+       <row>
+	<entry>
+	 <type>time with time zone</type>
+	 <literal>AT TIME ZONE</literal>
+	 <replaceable>zone</>
+	</entry>
+	<entry><type>time with time zone</type></entry>
+	<entry>Convert local time across timezones</entry>
+       </row>
+
+      </tbody>
+     </tgroup>
+    </table>
+
+   <para>
+    In these expressions, the desired time <replaceable>zone</> can be
+    specified either as a text string (e.g., <literal>'PST'</literal>)
+    or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
+   </para>
+
+   <para>
+    Examples (supposing that <varname>TimeZone</> is <literal>PST8PDT</>):
+<screen>
+SELECT TIMESTAMP '2001-02-16 20:38:40' AT TIME ZONE 'MST';
+<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';
+<lineannotation>Result: </lineannotation><computeroutput>2001-02-16 18:38:40</computeroutput>
+</screen>
+    The first example takes a zone-less timestamp and interprets it as MST time
+    (GMT-7) to produce a UTC timestamp, which is then rotated to PST (GMT-8)
+    for display.  The second example takes a timestamp specified in EST
+    (GMT-5) and converts it to local time in MST (GMT-7).
+   </para>
+
+   <para>
+    The function <function>timezone</function>(<replaceable>zone</>,
+    <replaceable>timestamp</>) is equivalent to the SQL-compliant construct
+    <replaceable>timestamp</> <literal>AT TIME ZONE</literal>
+    <replaceable>zone</>. 
+   </para>
+  </sect2>
+
   <sect2 id="functions-datetime-current">
    <title>Current Date/Time</title>
 
@@ -4219,6 +4313,16 @@ LOCALTIMESTAMP
 LOCALTIME ( <replaceable>precision</replaceable> )
 LOCALTIMESTAMP ( <replaceable>precision</replaceable> )
 </synopsis>
+    </para>
+
+    <para>
+     <function>CURRENT_TIME</function> and
+     <function>CURRENT_TIMESTAMP</function> deliver values with time zone;
+     <function>LOCALTIME</function> and
+     <function>LOCALTIMESTAMP</function> deliver values without time zone.
+    </para>
+
+    <para>
      <function>CURRENT_TIME</function>,
      <function>CURRENT_TIMESTAMP</function>,
      <function>LOCALTIME</function>, and