Re: Determining/Setting a server's time zone

On Mon, Mar 23, 2009 at 6:14 PM, Madison Kelly <linux(at)alteeve(dot)com> wrote:
> Tom Lane wrote:
>>
>> Madison Kelly <linux(at)alteeve(dot)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.