Thread: setting time zone during a transaction causes time warp
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. Once the transaction is committed,
everything goes back to normal. I've reproduced the problem below using
the "psql" client. The clock on my machine, which is running RedHat Linux
7.3, is set to UTC. The first, second, and fourth times in the output are
correct, but the third one is incorrect by five hours (which, perhaps
significantly, also happens to be the difference between the US/Eastern
time zone and the UTC time zone). I'm not quite sure what's going on
here, but if you know of a way to fix this or have a patch that I could
apply, I'd really appreciate it!
Thanks,
...Robert
P.S. Thanks for writing such great free software!
dev:~$ psql
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
pyramid=# select 'now'::timestamp;
timestamptz
------------------------------
2003-02-19 20:10:29.20276+00
(1 row)
pyramid=# begin work;
BEGIN
pyramid=# select 'now'::timestamp;
timestamptz
-------------------------------
2003-02-19 20:10:38.834418+00
(1 row)
pyramid=# set time zone 'US/Eastern';
SET VARIABLE
pyramid=# select 'now'::timestamp;
timestamptz
-------------------------------
2003-02-19 10:10:38.834418-05
(1 row)
pyramid=# commit work;
COMMIT
pyramid=# select 'now'::timestamp;
timestamptz
-------------------------------
2003-02-19 15:11:14.814469-05
(1 row)
pyramid=# \q
"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);
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@sss.pgh.pa.us>
02/20/2003 12:28 AM
To: Robert Haas/Tekconnect@Tekconnect
cc: pgsql-bugs@postgresql.org
Fax to:
Subject: Re: [BUGS] setting time zone during a transaction
causes time warp
"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);