Re: setting time zone during a transaction causes time warp - Mailing list pgsql-bugs

"Robert Haas" <Robert.Haas@tekconnect.com> writes:
> While using PostgreSQL 7.2.3, I discovered that if I set the time zone
> during the transaction, "now" takes on an incorrect value for the
> remainder of that transaction.

The problem seems to be restricted to the result of 'now'::timestamptz,
and not now() or 'now'::timestamp without time zone.  I've applied the
attached patch to 7.3 --- it would probably work in 7.2 as well,
although I think you'd have to hand-patch because of the difference in
the nearby HAVE_INT64_TIMESTAMP #ifdefs.

            regards, tom lane


*** src/backend/utils/adt/datetime.c~    Tue Jan 28 20:09:03 2003
--- src/backend/utils/adt/datetime.c    Thu Feb 20 00:19:50 2003
***************
*** 1242,1250 ****
                              case DTK_NOW:
                                  tmask = (DTK_DATE_M | DTK_TIME_M | DTK_M(TZ));
                                  *dtype = DTK_DATE;
!                                 GetCurrentTimeUsec(tm, fsec);
!                                 if (tzp != NULL)
!                                     *tzp = CTimeZone;
                                  break;

                              case DTK_YESTERDAY:
--- 1242,1248 ----
                              case DTK_NOW:
                                  tmask = (DTK_DATE_M | DTK_TIME_M | DTK_M(TZ));
                                  *dtype = DTK_DATE;
!                                 GetCurrentTimeUsec(tm, fsec, tzp);
                                  break;

                              case DTK_YESTERDAY:
***************
*** 1958,1964 ****
                              case DTK_NOW:
                                  tmask = DTK_TIME_M;
                                  *dtype = DTK_TIME;
!                                 GetCurrentTimeUsec(tm, fsec);
                                  break;

                              case DTK_ZULU:
--- 1956,1962 ----
                              case DTK_NOW:
                                  tmask = DTK_TIME_M;
                                  *dtype = DTK_TIME;
!                                 GetCurrentTimeUsec(tm, fsec, NULL);
                                  break;

                              case DTK_ZULU:
*** src/backend/utils/adt/nabstime.c~    Thu Dec 12 14:17:04 2002
--- src/backend/utils/adt/nabstime.c    Thu Feb 20 00:19:50 2003
***************
*** 243,267 ****
      int            tz;

      abstime2tm(GetCurrentTransactionStartTime(), &tz, tm, NULL);
-
-     return;
  }    /* GetCurrentDateTime() */


  void
! GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec)
  {
      int            tz;
      int            usec;

      abstime2tm(GetCurrentTransactionStartTimeUsec(&usec), &tz, tm, NULL);
  #ifdef HAVE_INT64_TIMESTAMP
      *fsec = usec;
  #else
      *fsec = usec * 1.0e-6;
  #endif
-
-     return;
  }    /* GetCurrentTimeUsec() */


--- 243,266 ----
      int            tz;

      abstime2tm(GetCurrentTransactionStartTime(), &tz, tm, NULL);
  }    /* GetCurrentDateTime() */


  void
! GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec, int *tzp)
  {
      int            tz;
      int            usec;

      abstime2tm(GetCurrentTransactionStartTimeUsec(&usec), &tz, tm, NULL);
+     /* Note: don't pass NULL tzp directly to abstime2tm */
+     if (tzp != NULL)
+         *tzp = tz;
  #ifdef HAVE_INT64_TIMESTAMP
      *fsec = usec;
  #else
      *fsec = usec * 1.0e-6;
  #endif
  }    /* GetCurrentTimeUsec() */


*** src/include/utils/datetime.h~    Wed Jan 15 19:27:17 2003
--- src/include/utils/datetime.h    Thu Feb 20 00:19:51 2003
***************
*** 261,267 ****


  extern void GetCurrentDateTime(struct tm * tm);
! extern void GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec);
  extern void j2date(int jd, int *year, int *month, int *day);
  extern int    date2j(int year, int month, int day);

--- 261,267 ----


  extern void GetCurrentDateTime(struct tm * tm);
! extern void GetCurrentTimeUsec(struct tm * tm, fsec_t *fsec, int *tzp);
  extern void j2date(int jd, int *year, int *month, int *day);
  extern int    date2j(int year, int month, int day);