Re: [HACKERS] Timezone bugs - Mailing list pgsql-patches

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>