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!

Before you make any changes, it's always good to check the current timezone value. Tips here: https://stackoverflow.com/a/28218103/625840 — Hartley Brody, Sep 08 '20 at 18:22

score 174 · Accepted Answer · edited May 19 '15 at 21:52

174

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.

edited May 19 '15 at 21:52

Basil Bourque

303,325
100
852
1,154

answered Jul 12 '11 at 12:01

Muhammad Usama

2,797
1
17
14

5

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 – Blu Towers Jul 12 '11 at 13:58
5

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. – Muhammad Usama Jul 12 '11 at 14:14
7

I have tried by changing the timezone to GMT in postgresql.conf and it seems to be working fine. – Muhammad Usama Jul 12 '11 at 14:15
More standard (SQL spec compliant) is two words for "TIME ZONE": `SET TIME ZONE 'UTC';`. See [the doc](http://www.postgresql.org/docs/current/static/sql-set.html). – Basil Bourque Dec 08 '14 at 03:52

score 160 · Answer 2 · edited Oct 04 '19 at 18:02

160

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.

edited Oct 04 '19 at 18:02

Muhammad Waheed

1,048
1
13
30

answered Aug 14 '15 at 11:56

Iurii Perevertailo

2,515
2
19
12

9

You need to restart the postgresql service after this is done – Joey Pinto Apr 18 '16 at 17:24
36

@JoeyPinto not true, it's enough to issue `SELECT pg_reload_conf();` :) – Alphaaa Oct 21 '16 at 16:58
7

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? – Noumenon Jul 23 '19 at 04:12
@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. – Prem popatia Sep 06 '20 at 15:21
@Prempopatia I believe you are connecting to different db than you altered. – Alex Rizvi Oct 02 '20 at 00:22
@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? – Prem popatia Oct 03 '20 at 09:30
2

@Prempopatia perhaps a noob question: Did you try refreshing pgadmin itself? – Alex Rizvi Oct 04 '20 at 05:43
2

@Noumenon For me, closing and opening the pgadmin helped. – user2678868 Feb 21 '21 at 13:57
This worked for me on Amazon RDS which hadn't updated the timezone even after fixing the parameter group. Thank you! – Abhinav Thakur Nov 26 '21 at 20:26
I had to relog to the server to make the change take effect – TheQuestioner May 05 '22 at 19:55

Basil Bourque · Answer 3 · 2020-07-10T22:57:28.497

73

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

edited Jul 10 '20 at 22:57

answered Dec 08 '14 at 04:16

Basil Bourque

303,325
100
852
1,154

16

bonus point for being the only answer to provide the `SHOW` part – Ariel Allon Aug 31 '17 at 05:04
2

Roger that, Ariel Allon -- and, for the next guy who wants to select that time zone into a variable... `SELECT current_setting('TIMEZONE')` – Wellspring Jul 22 '20 at 14:55
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`. – Mikko Rantalainen Dec 17 '21 at 11:43
@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. – Basil Bourque Dec 17 '21 at 16:56
@MikkoRantalainen I have no idea what is “default timezone for the database”. Please provide a link to documentation. – Basil Bourque Dec 17 '21 at 17:02
Like in `ALTER DATABASE postgres SET timezone TO 'Europe/Berlin';` – Mikko Rantalainen Dec 19 '21 at 13:05

score 63 · Answer 4 · edited Aug 17 '16 at 19:48

63

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

edited Aug 17 '16 at 19:48

chaserb

1,340
1
14
25

answered Jul 11 '12 at 23:41

guerrerocarlos

1,374
12
6

6

This is actually the correct answer as it makes the TZ change default for every process in PGSQL, not just current user. – jfreak53 May 08 '14 at 14:58
23

I suggest you **avoid those 3 or 4 letter codes** altogether. They are neither standardized nor unique. Instead use [proper time zone names](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones) (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`). – Basil Bourque Apr 29 '15 at 21:05
3

As per the [PostGRE docs](http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#DATATYPE-TIMEZONES), 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. – Seldom 'Where's Monica' Needy May 28 '15 at 21:36
I don't have that folder in share `postgresql` – SuperUberDuper Jul 18 '16 at 14:21
2

Instead of restart PostgreSQL, you can run `select pg_reload_conf()`. – ANeves Nov 09 '18 at 18:42
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. – blissweb Mar 22 '21 at 07:29
@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](https://en.wikipedia.org/wiki/Time_in_Australia) (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! – Philip Couling May 05 '22 at 20:44
Thank you, it just works 1. ·SELECT * FROM pg_timezone_names;· 2. change timezone in postgresql.conf 3. sudo /etc/init.d/postgresql reload – Sunding Wei May 12 '23 at 13:42

score 10 · Answer 5 · answered Sep 07 '16 at 09:32

10

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

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

answered Sep 07 '16 at 09:32

Pascal

1,984
1
21
25

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` – 1_bug May 12 '21 at 08:49

score 8 · Answer 6 · answered Jan 09 '18 at 18:57

8

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.

answered Jan 09 '18 at 18:57

ARA1307

1,022
10
13

so this needs to be kept in sync with the summer/winter time changes? – Cpt. Senkfuss Nov 20 '18 at 10:22
2

@Cpt.Senkfuss, I believe something like -Duser.timezone=Australia/Tasmania should also work and take care of summer\winter changes. – ARA1307 Nov 21 '18 at 02:38
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 – Olgun Kaya Feb 06 '20 at 06:18

score 4 · Answer 7 · edited May 13 '22 at 14:42

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

The same tricks worked for me in the Linux system. I did this with version 12. — Aupr, May 22 '23 at 09:54

score 1 · Answer 8 · answered Jan 16 '18 at 18:31

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)

Rui Lima · Answer 9 · 2021-02-27T18:57:28.993

0

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?

edited Feb 27 '21 at 18:57

answered Feb 27 '21 at 18:31

Rui Lima

7,185
4
31
42

score 0 · Answer 10 · answered Mar 24 '22 at 07:28

0

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

answered Mar 24 '22 at 07:28

vitams

585
6
7

postgres default timezone

10 Answers10

Configuration Parameter Name

Standard SQL Command

Time Zone Name

Get current zone

Linked

Related