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
