Обсуждение: Determining/Setting a server's time zone

Hi,

   How/Where does PostgreSQL set or determine the local time zone?

On my server, I am seeing (+00):

db=> SELECT now();
               now
-------------------------------
  2009-03-23 22:32:47.595491+00
(1 row)


But on my workstation I am seeing (-04):

db=> SELECT now();
               now
-------------------------------
  2009-03-23 18:16:36.591653-04
(1 row)

   The only thing I can think of is that my server is set to believe the
BIOS time is UTC and my workstation is set to EDT. Does PostgreSQL check
this from the host machine?

   For reference, my server is Debian Sarge (4.0) and my workstation is
Ubuntu 8.10.

Thanks!

Madi

Madison Kelly <linux@alteeve.com> writes:
>    How/Where does PostgreSQL set or determine the local time zone?

Well, "show timezone" will tell you what PG is using.  Where it came
from is a bit harder to answer.  The default is to use whatever
zone is current according to the postmaster's startup environment,
and that would depend on some factors you didn't tell us, like
how you're starting the postmaster.  Do your two machines report
the same timezone when you run "date" as a shell command?

The easy solution is to set the value you want in postgresql.conf.

            regards, tom lane

Tom Lane wrote:
> Madison Kelly <linux@alteeve.com> writes:
>>    How/Where does PostgreSQL set or determine the local time zone?
>
> Well, "show timezone" will tell you what PG is using.  Where it came
> from is a bit harder to answer.  The default is to use whatever
> zone is current according to the postmaster's startup environment,
> and that would depend on some factors you didn't tell us, like
> how you're starting the postmaster.  Do your two machines report
> the same timezone when you run "date" as a shell command?
>
> The easy solution is to set the value you want in postgresql.conf.
>
>             regards, tom lane

Hi Tom,

   'date' shows the same:

   Server (PostgreSQL 8.1):

$ date
Mon Mar 23 20:07:20 EDT 2009
db=> show timezone;
  TimeZone
----------
  GMT
(1 row)

   Workstation (PostgreSQL 8.3):

$ date
Mon Mar 23 20:07:09 EDT 2009
db=> show timezone;
  TimeZone
-----------
  localtime
(1 row)

   Neither has the environment variable 'TZ' set (at least, 'echo $TZ'
returns nothing). Also, 'cat /etc/postgresql/8.1/main/environment' has
no values on either machine. In both cases, the postmaster is started by
init.d. The only reference to time zone I could otherwise find was in
the 'postgresql.conf' file. Both are commented out with the comment that
timezone defaults to TZ.

   My concern with forcing a value in the postgresql.conf file is
forgetting to update the conf file when EDT/EST changes...

Thanks for the help so far!

Madi

On Mon, Mar 23, 2009 at 6:14 PM, Madison Kelly <linux@alteeve.com> wrote:
> Tom Lane wrote:
>>
>> Madison Kelly <linux@alteeve.com> writes:
>>>
>>>   How/Where does PostgreSQL set or determine the local time zone?
>>
>> Well, "show timezone" will tell you what PG is using.  Where it came
>> from is a bit harder to answer.  The default is to use whatever
>> zone is current according to the postmaster's startup environment,
>> and that would depend on some factors you didn't tell us, like
>> how you're starting the postmaster.  Do your two machines report
>> the same timezone when you run "date" as a shell command?
>>
>> The easy solution is to set the value you want in postgresql.conf.
>>
>>                        regards, tom lane
>
> Hi Tom,
>
>  'date' shows the same:
>
>  Server (PostgreSQL 8.1):
>
> $ date
> Mon Mar 23 20:07:20 EDT 2009
> db=> show timezone;
>  TimeZone
> ----------
>  GMT
> (1 row)
>
>  Workstation (PostgreSQL 8.3):
>
> $ date
> Mon Mar 23 20:07:09 EDT 2009
> db=> show timezone;
>  TimeZone
> -----------
>  localtime
> (1 row)
>
>  Neither has the environment variable 'TZ' set (at least, 'echo $TZ' returns
> nothing). Also, 'cat /etc/postgresql/8.1/main/environment' has no values on
> either machine. In both cases, the postmaster is started by init.d. The only
> reference to time zone I could otherwise find was in the 'postgresql.conf'
> file. Both are commented out with the comment that timezone defaults to TZ.
>
>  My concern with forcing a value in the postgresql.conf file is forgetting
> to update the conf file when EDT/EST changes...

As long as you pick a timezone that has is_dst set to true in the
pg_timezone_names table you'll be ok.

Madison Kelly <linux@alteeve.com> writes:
>    Server (PostgreSQL 8.1):

> $ date
> Mon Mar 23 20:07:20 EDT 2009
> db=> show timezone;
>   TimeZone
> ----------
>   GMT
> (1 row)

Hmm.  Apparently, this machine is configured so that TZ is set properly
in the environment of user login processes (perhaps in /etc/profile?)
but stuff launched from init sees TZ unset or set to GMT.  Messing with
the init environment might break some other program that wants it to be
GMT, so changing postgresql.conf is the best answer.

>    My concern with forcing a value in the postgresql.conf file is
> forgetting to update the conf file when EDT/EST changes...

As Scott said, you select a zone specification that is DST-aware;
you don't have to change it every six months, or indeed ever unless
you move.

            regards, tom lane