Skip site navigation (1) Skip section navigation (2)

Re: setting time zone during a transaction causes time warp

From: "Robert Haas" <Robert(dot)Haas(at)tekconnect(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: setting time zone during a transaction causes time warp
Date: 2003-03-03 21:01:45
Message-ID: OFDF737F92.186F3C11-ON85256CDE.0073584B-85256CDE.00738566@tekconnect.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Thanks for the quick response!  I haven't had a chance to test the patch 
yet (this is being built by an automated system, so hand-patching is a bit 
tough, but I'll adapt the patch to apply cleanly when I have some spare 
time...  or just upgrade to the latest version), but I just wanted you to 
know that I really appreciate the fast and thorough responses.

...Robert





Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
02/20/2003 12:28 AM
 
        To:     Robert Haas/Tekconnect(at)Tekconnect
        cc:     pgsql-bugs(at)postgresql(dot)org
        Fax to: 
        Subject:        Re: [BUGS] setting time zone during a transaction 
causes time warp


"Robert Haas" <Robert(dot)Haas(at)tekconnect(dot)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);
 

In response to

pgsql-bugs by date

Next:From: Donald FraserDate: 2003-03-03 22:13:10
Subject: Default values for nulls not being set.
Previous:From: Tom LaneDate: 2003-03-02 17:46:15
Subject: Re: [PORTS] Installation Errors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group