postgres default timezone

user_754703 0
Viewed 304966

I installed PostgreSQL 9 and the time it is showing is 1 hour behind the server time.

Running Select NOW() shows: 2011-07-12 11:51:50.453842+00

The server date shows: Tue Jul 12 12:51:40 BST 2011

It is 1 hour behind but the timezone shown in phppgadmin is: TimeZone Etc/GMT0

I have tried going into the postgresql.conf and setting

timezone = GMT

then running a restart but no change.

Any ideas I thought it would have just used the server timezone but obviously not?!

SOLUTION!: I did set to GMT before and it was an hour behind. after searching around turns out that I needed to set it to Europe/London. This takes into account the +1 hour in British summer time, GMT does not!

postgresql
10 Answers

Note many third-party clients have own timezone settings overlapping any Postgres server and\or session settings.

E.g. if you're using 'IntelliJ IDEA 2017.3' (or DataGrips), you should define timezone as:

'DB source properties' -> 'Advanced' tab -> 'VM Options': -Duser.timezone=UTC+06:00

otherwise you will see 'UTC' despite of whatever you have set anywhere else.

user_10172930

Maybe not related to the question, but I needed to use CST, set the system timezone to the desired tz (America/...) and then in postgresql conf set the value of the timezone to 'localtime' and it worked as a charm, current_time printing the right time (Postgresql 9.5 on Ubuntu 16)

user_32864700

What if you set the timezone of the role you are using?

ALTER ROLE my_db_user IN DATABASE my_database
    SET "TimeZone" TO 'UTC';

Will this be of any use?

user_5659770

For postgres 14+ solution: in file postgresql.conf timezone = 'Europe/Budapest'

user_20433850

For windows 10/11 users who would like to change it permanently the file is located in:

C:\Program Files\PostgreSQL\<your-postgres-version>\data\postgresql.conf

For me it was specifically:

C:\Program Files\PostgreSQL\14\data\postgresql.conf

Then in your text editor ctrl-f to find timezone and reset the following lines to your preferred timezone (name can be found by running SELECT * FROM pg_timezone_names):

log_timezone = 'UTC'

and

timezone = 'UTC'

in your Database GUI or CLI run the following query to reload the config file.

select pg_reload_conf()

confirm permanent changes by running:

show timezone
user_57486700
Related