setting time zone during a transaction causes time warp

From: "Robert Haas" <Robert(dot)Haas(at)tekconnect(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: setting time zone during a transaction causes time warp
Date: 2003-02-19 20:29:42
Message-ID: OF960F8B35.FA6C22F9-ON85256CD2.006F7FBE-85256CD2.0070B20D@tekconnect.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Bruce Momjian 2003-02-19 21:50:41 Re: MacOS X build --with-pam - bad directory for pam_appl.h
Previous Message Tom Lane 2003-02-19 16:18:19 Re: Trigger Crashes Database Back End.