postgres default timezone

Question

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!

Inamorato

Before you make any changes, it's always good to check the current timezone value. Tips here: https://mcmap.net/q/145033/-how-do-i-get-the-current-timezone-name-in-postgres-9-3 – Kennel

Answer 1

The time zone is a session parameter. So, you can change the timezone for the current session.

See the doc.

set timezone TO 'GMT';

---

Or, more closely following the SQL standard, use the SET TIME ZONE command. Notice two words for "TIME ZONE" where the code above uses a single word "timezone".

SET TIME ZONE 'UTC';

The doc explains the difference:

SET TIME ZONE extends syntax defined in the SQL standard. The standard allows only numeric time zone offsets while PostgreSQL allows more flexible time-zone specifications. All other SET features are PostgreSQL extensions.

Astra

I have tried setting that and it didnt seem to work, also doesnt that only set it for the active session. I wish to change it permanently for all databases ect, I did this easily in phpmyadmin but cant seem to find a way to do it for postgresql – Inamorato

yup 'set timezone To ..' only sets the timezone for the current session and if you change the timezone configuration parameter in Postgresql.conf it should change the timezone for all databases. – Astra

I have tried by changing the timezone to GMT in postgresql.conf and it seems to be working fine. – Astra

More standard (SQL spec compliant) is two words for "TIME ZONE": SET TIME ZONE 'UTC';. See the doc. – Ballman

Answer 2

Choose a timezone from:

SELECT * FROM pg_timezone_names;

And set as below given example:

ALTER DATABASE postgres SET timezone TO 'Europe/Berlin';

Use your DB name in place of postgres in above statement.

Berkshire

You need to restart the postgresql service after this is done – Hathorn

@JoeyPinto not true, it's enough to issue SELECT pg_reload_conf(); :) – Hydrogenolysis

I ran the statement with my database name and SELECT pg_reload_conf() returned true, but now() and select current_setting('TIMEZONE') continue to return values for 'America/New_York'. Is it because I'm not superuser? – Apocrine

@JoeyPinto thanks for suggestion, i tried with SELECT pg_reload_conf() and it also returned true but it didnt showed updated timezone, after restarting the service of postgres, it reflected new timezone. – Polymath

@Prempopatia I believe you are connecting to different db than you altered. – Cavill

@AlexRizvi I have tried again to make sure that i am connecting correct db and altering the same db itself, but it seems that executing SELECT pg_reload_conf(); query doesnt change the timezone on the fly. Postgresql version: 12.4 and i am using pgAdmin version 4.25. am I missing something because of which it is not updating timezone on the fly with given query? – Polymath

@Prempopatia perhaps a noob question: Did you try refreshing pgadmin itself? – Cavill

@Apocrine For me, closing and opening the pgadmin helped. – Imogeneimojean

This worked for me on Amazon RDS which hadn't updated the timezone even after fixing the parameter group. Thank you! – Violative

I had to relog to the server to make the change take effect – Dippy

Answer 3

The accepted answer by Muhammad Usama is correct.

Configuration Parameter Name

That answer shows how to set a Postgres-specific configuration parameter with the following:

SET timezone TO 'UTC';

…where timezone is not a SQL command, it is the name of the configuration parameter.

See the doc for this.

Standard SQL Command

Alternatively, you can use the SQL command defined by the SQL spec: SET TIME ZONE. In this syntax a pair of words TIME ZONE replace "timezone" (actual SQL command versus parameter name), and there is no "TO".

SET TIME ZONE 'UTC';

Both this command and the one above have the same effect, to set the value for the current session only. To make the change permanent, see this sibling answer.

See the doc for this.

Time Zone Name

You can specify a proper time zone name. Most of these are continent/region.

SET TIME ZONE 'Africa/Casablanca';

…or…

SET TIME ZONE 'America/Montreal';

Avoid the 3 or 4 letter abbreviations such as EST or IST as they are neither standardized nor unique. See Wikipedia for list of time zone names.

Get current zone

To see the current time zone for a session, try either of the following statements. Technically we are calling the SHOW command to display a run-time parameter.

SHOW timezone ;

…or…

SHOW time zone ;

US/Pacific

Ballman

bonus point for being the only answer to provide the SHOW part – Burra

Roger that, Ariel Allon -- and, for the next guy who wants to select that time zone into a variable... SELECT current_setting('TIMEZONE') – Sofar

Note that this answer is about setting timezone for the current session which is different from the default timezone for the database. In addition, different columns may have different timezones, too, or even each timestamp in a column of type timestamp with time zone. – Kmeson

@MikkoRantalainen No, incorrect, Postgres does not save any time zone info that may have been provided with an input. Any such time zone info is used to adjust into UTC (an offset of zero). After adjustment, Postgres discards that time zone info. All values in every column of type TIMESTAMP WITH TIME ZONE is in UTC, has an offset of zero. If you want to retain the original time zone for each input, you must save that to an additional column. – Ballman

@MikkoRantalainen I have no idea what is “default timezone for the database”. Please provide a link to documentation. – Ballman

Like in ALTER DATABASE postgres SET timezone TO 'Europe/Berlin'; – Kmeson

if show timezone; or show time zone; works, SET TIMEZONE… doesn't work, only SET TIME ZONE 'Europe/Paris'; works – Salverform

Answer 4

To acomplish the timezone change in Postgres 9.1 you must:

1.- Search in your "timezones" folder in /usr/share/postgresql/9.1/ for the appropiate file, in my case would be "America.txt", in it, search for the closest location to your zone and copy the first letters in the left column.

For example: if you are in "New York" or "Panama" it would be "EST":

#  - EST: Eastern Standard Time (Australia)
EST    -18000    # Eastern Standard Time (America)
                 #     (America/New_York)
                 #     (America/Panama)

2.- Uncomment the "timezone" line in your postgresql.conf file and put your timezone as shown:

#intervalstyle = 'postgres'
#timezone = '(defaults to server environment setting)'
timezone = 'EST'
#timezone_abbreviations = 'EST'     # Select the set of available time zone
                                        # abbreviations.  Currently, there are
                                        #   Default
                                        #   Australia

3.- Restart Postgres

Cere

This is actually the correct answer as it makes the TZ change default for every process in PGSQL, not just current user. – Shod

I suggest you avoid those 3 or 4 letter codes altogether. They are neither standardized nor unique. Instead use proper time zone names (continent SLASH city-or-region). To use your own example, Panama uses an offset of −05:00 year-round while New York shifts an hour with Daylight Saving Time (DST). A time zone is more than an offset; a time zone includes the past, present, and future set of rules and anomalies such as DST. So, say what you mean: America/Panama, America/New_York, Europe/London, UTC (or Zulu). – Ballman

As per the PostGRE docs, you can check an internal view to get a list of the recognized timezone names in your specific database by doing SELECT * FROM pg_timezone_names which is probably safer to do, given 3rd-party sites won't necessarily be as up-to-date (or out-of-date) as your own particular database instance. – Mensa

I don't have that folder in share postgresql – Secondly

Instead of restart PostgreSQL, you can run select pg_reload_conf(). – Basis

Some notes I've found: If you comment out all timezone lines in the config file it defaults to GMT, not the system time. If you set the timezone to "Host", it also doesn't seem to match the system time either. So, an explicit timezone like "America/Phoenix" followed by a restart works like a charm, using the Wikipedia list of zones. – Meantime

@BasilBourque that advice is contradictory and the part in bold is not good advice. "avoid those 3 or 4 letter codes" is mutually exclusive with "say what you mean". I'm working on an example when the correct timezone is AEST (UTC+10). My colleagues have assumed your first statement and found the only IANA name that seems to match (Australia/Queensland). Only the timezone we must work in is AEST regardless of acts of government in Queensland! – Dithyrambic

Thank you, it just works 1. ·SELECT * FROM pg_timezone_names;· 2. change timezone in postgresql.conf 3. sudo /etc/init.d/postgresql reload – Globule

Answer 5

In addition to the previous answers, if you use the tool pgAdmin III you can set the time zone as follows:

1. Open Postgres config via Tools > Server Configuration > postgresql.conf
2. Look for the entry timezone and double click to open
3. Change the Value
4. Reload Server to apply configuration changes (Play button on top or via services)

Echidna

In my experience it is a very dangerous way - very often, after changed configuration via pgAdmin tool, the postgresql service start throw errors. Instead of using a pgAdmin for it I prefer edit a postgresql.conf raw file (placed in C:\Program Files\PostgreSQL\(version)\data – Halla

Answer 6

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.

Hampshire

so this needs to be kept in sync with the summer/winter time changes? – Magnesia

@Cpt.Senkfuss, I believe something like -Duser.timezone=Australia/Tasmania should also work and take care of summer\winter changes. – Hampshire

This is a life saver trick. I have been trying to undstand what's wrong may be for 3 hours by now :) btw, tried the full time zone name and it works. for me it is -Duser.timezone=Europe/Istanbul – Gutta

Answer 7

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

Testicle

The same tricks worked for me in the Linux system. I did this with version 12. – Olmsted

Answer 8

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)

Instauration

Answer 9

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?

Dioptometer

Answer 10

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

Chape