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

Andrew pointed out that the current fix didn't handle dates that were
near daylight savings time boudaries.  This handles it properly, e.g.

    test=> select '2005-04-03 04:00:00'::timestamp at time zone
    'America/Los_Angeles';
            timezone
    ------------------------
     2005-04-03 07:00:00-04
    (1 row)

Patch attached and applied.  The new fix is cleaner too.

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

pgman wrote:
>
> 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

--
  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: src/backend/utils/adt/date.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/date.c,v
retrieving revision 1.118
diff -c -c -r1.118 date.c
*** src/backend/utils/adt/date.c    22 Jul 2005 05:03:09 -0000    1.118
--- src/backend/utils/adt/date.c    23 Jul 2005 14:23:14 -0000
***************
*** 301,307 ****
      tm->tm_hour = 0;
      tm->tm_min = 0;
      tm->tm_sec = 0;
!     tz = DetermineLocalTimeZone(tm);

  #ifdef HAVE_INT64_TIMESTAMP
      result = dateVal * USECS_PER_DAY + tz * USECS_PER_SEC;
--- 301,307 ----
      tm->tm_hour = 0;
      tm->tm_min = 0;
      tm->tm_sec = 0;
!     tz = DetermineTimeZoneOffset(tm, global_timezone);

  #ifdef HAVE_INT64_TIMESTAMP
      result = dateVal * USECS_PER_DAY + tz * USECS_PER_SEC;
***************
*** 2231,2237 ****

      GetCurrentDateTime(tm);
      time2tm(time, tm, &fsec);
!     tz = DetermineLocalTimeZone(tm);

      result = (TimeTzADT *) palloc(sizeof(TimeTzADT));

--- 2231,2237 ----

      GetCurrentDateTime(tm);
      time2tm(time, tm, &fsec);
!     tz = DetermineTimeZoneOffset(tm, global_timezone);

      result = (TimeTzADT *) palloc(sizeof(TimeTzADT));

Index: src/backend/utils/adt/datetime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v
retrieving revision 1.156
diff -c -c -r1.156 datetime.c
*** src/backend/utils/adt/datetime.c    22 Jul 2005 03:46:33 -0000    1.156
--- src/backend/utils/adt/datetime.c    23 Jul 2005 14:23:15 -0000
***************
*** 1612,1618 ****
              if (fmask & DTK_M(DTZMOD))
                  return DTERR_BAD_FORMAT;

!             *tzp = DetermineLocalTimeZone(tm);
          }
      }

--- 1612,1618 ----
              if (fmask & DTK_M(DTZMOD))
                  return DTERR_BAD_FORMAT;

!             *tzp = DetermineTimeZoneOffset(tm, global_timezone);
          }
      }

***************
*** 1620,1629 ****
  }


! /* DetermineLocalTimeZone()
   *
   * Given a struct pg_tm in which tm_year, tm_mon, tm_mday, tm_hour, tm_min, and
!  * tm_sec fields are set, attempt to determine the applicable local zone
   * (ie, regular or daylight-savings time) at that time.  Set the struct pg_tm's
   * tm_isdst field accordingly, and return the actual timezone offset.
   *
--- 1620,1629 ----
  }


! /* DetermineTimeZoneOffset()
   *
   * Given a struct pg_tm in which tm_year, tm_mon, tm_mday, tm_hour, tm_min, and
!  * tm_sec fields are set, attempt to determine the applicable time zone
   * (ie, regular or daylight-savings time) at that time.  Set the struct pg_tm's
   * tm_isdst field accordingly, and return the actual timezone offset.
   *
***************
*** 1632,1638 ****
   * of mktime(), anyway.
   */
  int
! DetermineLocalTimeZone(struct pg_tm *tm)
  {
      int            date,
                  sec;
--- 1632,1638 ----
   * of mktime(), anyway.
   */
  int
! DetermineTimeZoneOffset(struct pg_tm *tm, pg_tz *tzp)
  {
      int            date,
                  sec;
***************
*** 1648,1654 ****
                  after_isdst;
      int            res;

!     if (HasCTZSet)
      {
          tm->tm_isdst = 0;        /* for lack of a better idea */
          return CTimeZone;
--- 1648,1654 ----
                  after_isdst;
      int            res;

!     if (tzp == global_timezone && HasCTZSet)
      {
          tm->tm_isdst = 0;        /* for lack of a better idea */
          return CTimeZone;
***************
*** 1687,1693 ****
                                 &before_gmtoff, &before_isdst,
                                 &boundary,
                                 &after_gmtoff, &after_isdst,
!                                global_timezone);
      if (res < 0)
          goto overflow;            /* failure? */

--- 1687,1693 ----
                                 &before_gmtoff, &before_isdst,
                                 &boundary,
                                 &after_gmtoff, &after_isdst,
!                                tzp);
      if (res < 0)
          goto overflow;            /* failure? */

***************
*** 2282,2288 ****
          tmp->tm_hour = tm->tm_hour;
          tmp->tm_min = tm->tm_min;
          tmp->tm_sec = tm->tm_sec;
!         *tzp = DetermineLocalTimeZone(tmp);
          tm->tm_isdst = tmp->tm_isdst;
      }

--- 2282,2288 ----
          tmp->tm_hour = tm->tm_hour;
          tmp->tm_min = tm->tm_min;
          tmp->tm_sec = tm->tm_sec;
!         *tzp = DetermineTimeZoneOffset(tmp, global_timezone);
          tm->tm_isdst = tmp->tm_isdst;
      }

Index: src/backend/utils/adt/formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.92
diff -c -c -r1.92 formatting.c
*** src/backend/utils/adt/formatting.c    21 Jul 2005 03:56:16 -0000    1.92
--- src/backend/utils/adt/formatting.c    23 Jul 2005 14:23:17 -0000
***************
*** 2989,2995 ****

      do_to_timestamp(date_txt, fmt, &tm, &fsec);

!     tz = DetermineLocalTimeZone(&tm);

      if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
          ereport(ERROR,
--- 2989,2995 ----

      do_to_timestamp(date_txt, fmt, &tm, &fsec);

!     tz = DetermineTimeZoneOffset(&tm, global_timezone);

      if (tm2timestamp(&tm, fsec, &tz, &result) != 0)
          ereport(ERROR,
Index: src/backend/utils/adt/nabstime.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/nabstime.c,v
retrieving revision 1.141
diff -c -c -r1.141 nabstime.c
*** src/backend/utils/adt/nabstime.c    22 Jul 2005 19:55:50 -0000    1.141
--- src/backend/utils/adt/nabstime.c    23 Jul 2005 14:23:18 -0000
***************
*** 474,480 ****
          result = NOEND_ABSTIME;
      else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
      {
!         tz = DetermineLocalTimeZone(tm);
          result = tm2abstime(tm, tz);
      }
      else
--- 474,480 ----
          result = NOEND_ABSTIME;
      else if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) == 0)
      {
!         tz = DetermineTimeZoneOffset(tm, global_timezone);
          result = tm2abstime(tm, tz);
      }
      else
Index: src/backend/utils/adt/timestamp.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/timestamp.c,v
retrieving revision 1.143
diff -c -c -r1.143 timestamp.c
*** src/backend/utils/adt/timestamp.c    23 Jul 2005 02:02:27 -0000    1.143
--- src/backend/utils/adt/timestamp.c    23 Jul 2005 14:23:19 -0000
***************
*** 2100,2106 ****
              if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
                  tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);

!             tz = DetermineLocalTimeZone(tm);

              if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
                  ereport(ERROR,
--- 2100,2106 ----
              if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
                  tm->tm_mday = (day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]);

!             tz = DetermineTimeZoneOffset(tm, global_timezone);

              if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
                  ereport(ERROR,
***************
*** 2124,2130 ****
              julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
              j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);

!             tz = DetermineLocalTimeZone(tm);

              if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
                  ereport(ERROR,
--- 2124,2130 ----
              julian = date2j(tm->tm_year, tm->tm_mon, tm->tm_mday) + span->day;
              j2date(julian, &tm->tm_year, &tm->tm_mon, &tm->tm_mday);

!             tz = DetermineTimeZoneOffset(tm, global_timezone);

              if (tm2timestamp(tm, fsec, &tz, ×tamp) != 0)
                  ereport(ERROR,
***************
*** 3104,3110 ****
          }

          if (redotz)
!             tz = DetermineLocalTimeZone(tm);

          if (tm2timestamp(tm, fsec, &tz, &result) != 0)
              ereport(ERROR,
--- 3104,3110 ----
          }

          if (redotz)
!             tz = DetermineTimeZoneOffset(tm, global_timezone);

          if (tm2timestamp(tm, fsec, &tz, &result) != 0)
              ereport(ERROR,
***************
*** 3529,3535 ****
                             (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                              errmsg("timestamp out of range")));

!                     tz = DetermineLocalTimeZone(tm);

                      if (tm2timestamp(tm, fsec, &tz, ×tamptz) != 0)
                          ereport(ERROR,
--- 3529,3535 ----
                             (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                              errmsg("timestamp out of range")));

!                     tz = DetermineTimeZoneOffset(tm, global_timezone);

                      if (tm2timestamp(tm, fsec, &tz, ×tamptz) != 0)
                          ereport(ERROR,
***************
*** 3924,3935 ****

  /*     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)
--- 3924,3934 ----

  /*     timestamp_zone()
   *     Encode timestamp type with specified time zone.
!  *     This function is just timestamp2timestamptz() except instead of
!  *    shifting to the global timezone, we shift to the specified timezone.
!  *    This is different from the other AT TIME ZONE cases because instead
!  *    of shifting to a _to_ a new time zone, it sets the time to _be_ the
!  *    specified timezone.
   */
  Datum
  timestamp_zone(PG_FUNCTION_ARGS)
***************
*** 3943,3953 ****
      int         len;
      struct pg_tm tm;
      fsec_t      fsec;
!
      if (TIMESTAMP_NOT_FINITE(timestamp))
          PG_RETURN_TIMESTAMPTZ(timestamp);

!     /* Find the specified timezone? */
      len = (VARSIZE(zone) - VARHDRSZ>TZ_STRLEN_MAX) ?
              TZ_STRLEN_MAX : VARSIZE(zone) - VARHDRSZ;
      memcpy(tzname, VARDATA(zone), len);
--- 3942,3953 ----
      int         len;
      struct pg_tm tm;
      fsec_t      fsec;
!     bool        fail;
!
      if (TIMESTAMP_NOT_FINITE(timestamp))
          PG_RETURN_TIMESTAMPTZ(timestamp);

!     /* Find the specified timezone */
      len = (VARSIZE(zone) - VARHDRSZ>TZ_STRLEN_MAX) ?
              TZ_STRLEN_MAX : VARSIZE(zone) - VARHDRSZ;
      memcpy(tzname, VARDATA(zone), len);
***************
*** 3963,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),
--- 3963,3975 ----
      }

      /* Apply the timezone change */
!     fail = (timestamp2tm(timestamp, NULL, &tm, &fsec, NULL, tzp) != 0);
!     if (!fail)
!     {
!         tz = DetermineTimeZoneOffset(&tm, tzp);
!         fail = (tm2timestamp(&tm, fsec, &tz, &result) != 0);
!     }
!     if (fail)
      {
          ereport(ERROR,
                  (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
***************
*** 3972,3979 ****
                          tzname)));
          PG_RETURN_NULL();
      }
-     /* Must double-adjust for timezone */
-     result = dt2local(result, -tz);

      PG_RETURN_TIMESTAMPTZ(result);
  }
--- 3977,3982 ----
***************
*** 4039,4045 ****
                      (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                       errmsg("timestamp out of range")));

!         tz = DetermineLocalTimeZone(tm);

          if (tm2timestamp(tm, fsec, &tz, &result) != 0)
              ereport(ERROR,
--- 4042,4048 ----
                      (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
                       errmsg("timestamp out of range")));

!         tz = DetermineTimeZoneOffset(tm, global_timezone);

          if (tm2timestamp(tm, fsec, &tz, &result) != 0)
              ereport(ERROR,
Index: src/include/utils/datetime.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/utils/datetime.h,v
retrieving revision 1.55
diff -c -c -r1.55 datetime.h
*** src/include/utils/datetime.h    22 Jul 2005 03:46:34 -0000    1.55
--- src/include/utils/datetime.h    23 Jul 2005 14:23:20 -0000
***************
*** 291,297 ****
  extern void DateTimeParseError(int dterr, const char *str,
                     const char *datatype);

! extern int    DetermineLocalTimeZone(struct pg_tm *tm);

  extern int    EncodeDateOnly(struct pg_tm *tm, int style, char *str);
  extern int    EncodeTimeOnly(struct pg_tm *tm, fsec_t fsec, int *tzp, int style, char *str);
--- 291,297 ----
  extern void DateTimeParseError(int dterr, const char *str,
                     const char *datatype);

! extern int    DetermineTimeZoneOffset(struct pg_tm *tm, pg_tz *tzp);

  extern int    EncodeDateOnly(struct pg_tm *tm, int style, char *str);
  extern int    EncodeTimeOnly(struct pg_tm *tm, fsec_t fsec, int *tzp, int style, char *str);