PostgreSQL timezone does not match system timezone

Solution 1:

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

• 9.1 TimeZone:

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

• 9.2 TimeZone:

(...) 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).