Why would select localtimestamp return different timezones on 2 different machines with Postgres

Question

If I execute on Postgres 9.2

show timezone

on my machine, it returns the local time zone

America/Denver

when I run the same postgres command on the guy beside me, it returns

US/Eastern

Anyone know why the Postgres Server would use two different timezones for 2 different people?

maybe you're using different settings, so go back to the default: set timezone to default; — Frank Heikens, Dec 09 '14 at 08:07

score 0 · Answer 1 · answered Dec 09 '14 at 11:17

The statement show time zone shows the time zone of the current client's session. That might or might not be the time zone of the PostgreSQL server.

To see how this works, open two terminal sessions on the same computer, and connect each of them to your database with psql. (I'm connecting to a database named "sandbox". My PostgreSQL server is set to local time. That is, postgresql.conf has the line timezone = 'localtime'.)

In the first session, set the time zone to Berkeley, Calif.

sandbox=# set time zone 'PST8PDT';

(The default scope for set is session. This statement is equivalent to set session time zone 'PST8PDT';.)

In the second session, set the time zone to UTC.

sandbox=# set time zone 'UTC';

Neither of these are my local time zone.

Now run show time zone in each session. The first one will return 'PST8PDT'; the second will return 'UTC'. The server's time zone is still local time.

If I drop one of the connections, and I connect again, I go back to local time.

sandbox=# show time zone;
 TimeZone  
-----------
 localtime

Why would select localtimestamp return different timezones on 2 different machines with Postgres

1 Answers1