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
