OK, tricky, but fixed --- patch attached and applied, with documentation
updates.  Here is the test query:

        test=> select (CURRENT_DATE + '05:00'::time)::timestamp at time zone
        'Canada/Pacific';
                timezone
        ------------------------
         2005-07-22 08:00:00-04
        (1 row)
        
I tested a bunch of others too, like:

        test=> select ('2005-07-20 00:00:00'::timestamp without time zone) at
        time zone 'Europe/Paris';
                timezone
        ------------------------
         2005-07-19 18:00:00-04
        (1 row)
        
and tested that for UTC also.

It was hard to figure out how to cleanly adjust the time zone.  I added
some comments explaining the process.

---------------------------------------------------------------------------

Andrew - Supernews wrote:
> On 2005-07-22, Bruce Momjian <pgman@candle.pha.pa.us> wrote:
> >> 
> >> select (CURRENT_DATE + '05:00'::time)::timestamp
> >>    at time zone 'Canada/Pacific';
> >>         timezone        
> >> ------------------------
> >>  2005-07-19 22:00:00+00
> >> (1 row)
> >> 
> > What is happening here is that 2005-07-20 05:00:00 is being cast back 7
> > hours (Canada/Pacific offset), and that is 22:00 of the previous day.
> 
> Which is of course completely wrong.
> 
> Let's look at what should happen:
> 
>  (date + time) = timestamp without time zone
> 
> '2005-07-20' + '05:00' = '2005-07-20 05:00:00'::timestamp
> 
>  (timestamp without time zone) AT TIME ZONE 'zone'
> 
> When AT TIME ZONE is applied to a timestamp without time zone, it is
> supposed to keep the _same_ calendar time and return a result of type
> timestamp with time zone designating the absolute time. So in this case,
> we expect the following to happen:
> 
>  '2005-07-20 05:00:00'              (original timestamp)
>  -> '2005-07-20 05:00:00-0700'      (same calendar time in new zone)
>  -> '2005-07-20 12:00:00+0000'      (convert to client timezone (UTC))
> 
> So the conversion is being done backwards, resulting in the wrong result.
> 
> -- 
> Andrew, Supernews
> http://www.supernews.com - individual and corporate NNTP services
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
Index: timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.141
diff -c -c -r1.141 timestamp.c
*** timestamp.c 22 Jul 2005 19:00:54 -0000      1.141
--- timestamp.c 23 Jul 2005 02:00:07 -0000
***************
*** 3922,3938 ****
  }
  
  
! /* timestamp_zone()
!  * Encode timestamp type with specified time zone.
!  * Returns timestamp with time zone, with the input
   *    rotated from local time to the specified zone.
   */
  Datum
  timestamp_zone(PG_FUNCTION_ARGS)
  {
        text       *zone = PG_GETARG_TEXT_P(0);
        Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
!       Timestamp result;
        int                     tz;
        pg_tz      *tzp;
        char        tzname[TZ_STRLEN_MAX+1];
--- 3922,3942 ----
  }
  
  
! /*    timestamp_zone()
!  *    Encode timestamp type with specified time zone.
!  *    Returns timestamp with time zone, with the input
   *    rotated from local time to the specified zone.
+  *    This function is tricky because instead of shifting
+  *    the time _to_ a new time zone, it sets the time to _be_
+  *    the specified timezone.  This requires trickery
+  *    of double-subtracting the requested timezone offset.
   */
  Datum
  timestamp_zone(PG_FUNCTION_ARGS)
  {
        text       *zone = PG_GETARG_TEXT_P(0);
        Timestamp timestamp = PG_GETARG_TIMESTAMP(1);
!       TimestampTz     result;
        int                     tz;
        pg_tz      *tzp;
        char        tzname[TZ_STRLEN_MAX+1];
***************
*** 3960,3966 ****
  
        /* Apply the timezone change */
        if (timestamp2tm(timestamp, &tz, &tm, &fsec, NULL, tzp) != 0 ||
!           tm2timestamp(&tm, fsec, NULL, &result) != 0)
        {
                ereport(ERROR,
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
--- 3964,3970 ----
  
        /* Apply the timezone change */
        if (timestamp2tm(timestamp, &tz, &tm, &fsec, NULL, tzp) != 0 ||
!           tm2timestamp(&tm, fsec, &tz, &result) != 0)
        {
                ereport(ERROR,
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
***************
*** 3968,3974 ****
                                        tzname)));
                PG_RETURN_NULL();
        }
!       PG_RETURN_TIMESTAMPTZ(timestamp2timestamptz(result));
  }
  
  /* timestamp_izone()
--- 3972,3981 ----
                                        tzname)));
                PG_RETURN_NULL();
        }
!       /* Must double-adjust for timezone */
!       result = dt2local(result, -tz);
! 
!       PG_RETURN_TIMESTAMPTZ(result);
  }
  
  /* timestamp_izone()
Index: func.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.268
retrieving revision 1.269
diff -c -c -r1.268 -r1.269
*** func.sgml   20 Jul 2005 16:42:29 -0000      1.268
--- func.sgml   22 Jul 2005 21:16:14 -0000      1.269
***************
*** 5693,5699 ****
           <literal><type>timestamp without time zone</type> AT TIME ZONE 
<replaceable>zone</></literal>
          </entry>
          <entry><type>timestamp with time zone</type></entry>
!         <entry>Convert local time in given time zone to UTC</entry>
         </row>
  
         <row>
--- 5693,5699 ----
           <literal><type>timestamp without time zone</type> AT TIME ZONE 
<replaceable>zone</></literal>
          </entry>
          <entry><type>timestamp with time zone</type></entry>
!         <entry>Treat given timestamp <emphasis>without time zone</> as 
located in the specified time zone</entry>
         </row>
  
         <row>
***************
*** 5701,5707 ****
           <literal><type>timestamp with time zone</type> AT TIME ZONE 
<replaceable>zone</></literal>
          </entry>
          <entry><type>timestamp without time zone</type></entry>
!         <entry>Convert UTC to local time in given time zone</entry>
         </row>
  
         <row>
--- 5701,5707 ----
           <literal><type>timestamp with time zone</type> AT TIME ZONE 
<replaceable>zone</></literal>
          </entry>
          <entry><type>timestamp without time zone</type></entry>
!         <entry>Convert given timestamp <emphasis>with time zone</> to the new 
time zone</entry>
         </row>
  
         <row>
***************
*** 5709,5715 ****
           <literal><type>time with time zone</type> AT TIME ZONE 
<replaceable>zone</></literal>
          </entry>
          <entry><type>time with time zone</type></entry>
!         <entry>Convert local time across time zones</entry>
         </row>
        </tbody>
       </tgroup>
--- 5709,5715 ----
           <literal><type>time with time zone</type> AT TIME ZONE 
<replaceable>zone</></literal>
          </entry>
          <entry><type>time with time zone</type></entry>
!         <entry>Convert given time <emphasis>with time zone</> to the new time 
zone</entry>
         </row>
        </tbody>
       </tgroup>
***************
*** 5720,5726 ****
      specified either as a text string (e.g., <literal>'PST'</literal>)
      or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
      In the text case, the available zone names are those shown in
!     <xref linkend="datetime-timezone-set-table">.
     </para>
  
     <para>
--- 5720,5727 ----
      specified either as a text string (e.g., <literal>'PST'</literal>)
      or as an interval (e.g., <literal>INTERVAL '-08:00'</literal>).
      In the text case, the available zone names are those shown in
!     <xref linkend="datetime-timezone-set-table">.  The time zone can
!     also be implied using the default time zone for that session.
     </para>
  
     <para>
***************
*** 5732,5741 ****
  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 time stamp and interprets it as MST 
time
!     (UTC-7) to produce a UTC time stamp, which is then rotated 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>
--- 5733,5741 ----
  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>
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Reply via email to