Home / server / Questions / 554359
Accepted
Martin C.
Asked: 2013-11-13 00:17:21 +0800 CST

PostgreSQL timezone does not match system timezone

  • 772

I have several PostgreSQL 9.2 installations where the timezone used by PostgreSQL is GMT, despite the entire system being "Europe/Vienna". I double-checked that postgresql.conf does not contain timezone setting, so according to the documentation it should fallback to the system's timezone.

However,

# su -s /bin/bash postgres -c "psql mydb"

mydb=# show timezone;
 TimeZone 
----------
 GMT
(1 row)

mydb=# select now();
              now              
-------------------------------
 2013-11-12 08:14:21.697622+00
(1 row)

Any hints, where the GMT timezone could come from? The system user does not have TZ set and the /etc/timezone and /etc/timeinfo seem to be configured correctly.

# cat /etc/timezone 
Europe/Vienna
# date
Tue Nov 12 09:15:42 CET 2013

Any hints are appreciated, thanks in advance!

postgresql

2 Answers

  1. Best Answer

    The default value for the TimeZone setting has changed on release 9.2:

    (..) If not explicitly set, the server initializes this variable to the time zone specified by its system environment. (...)

    (...) The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. (...)

    Which means that prior to version 9.2 the default value at postgresql.conf should be set during initdb phase. If you overridden that value (probably copying the old postgresql.conf while upgrading from older versions) PostgreSQL will use the "GMT" value as default.

    The solution for your case is quite simple, just change the TimeZone setting on postgresql.conf to the value you want:

    TimeZone = 'Europe/Vienna'

    After that you need to reload the service:

    # su - postgres -c "psql mydb -c 'SELECT pg_reload_conf()'"

    Then all fields stored as timestamp with time zone (or timestamptz) will be shown correctly from now on. But you will have to correct by hand all (update) the fields stored as timestamp without time zone (or timestamp).

    A tip I give to everyone upgrading PostgreSQL is not to copy the old postgresql.conf to the new cluster (notice I'm not sure if it what you did, but I saw this very same problem a lot because of that). Just get the one generated by initdb and add the modifications (a diff tool may be handful to this task).

    • 25

  2. I found a workaround for this.

    just create a symlink inside /usr/share/zoneinfo/ named localtime (or whatever name you wish) to be pointing at /etc/localtime

    /usr/share/zoneinfo/localtime -> /etc/localtime

    this way you're creating a string of links that ultimately points at your system's timezone.

    /etc/localtime -> /usr/share/zoneinfo/America/Los_Angeles

    Now take the name of the link you created (localtime in my case) and use it as the value of the configuration item in postgresql.conf

    TimeZone = 'localtime'

    restart postgresql and check the time with "SELECT now();" and "show timezone;"

    • 0