188

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!

Improve this question
1

10 Answers 10

Reset to default
207

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

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
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.
I have tried by changing the timezone to GMT in postgresql.conf and it seems to be working fine.
More standard (SQL spec compliant) is two words for "TIME ZONE": SET TIME ZONE 'UTC';. See the doc.
180

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.

Improve this answer

11 Comments

You need to restart the postgresql service after this is done
@JoeyPinto not true, it's enough to issue SELECT pg_reload_conf(); :)
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?
@Prempopatia perhaps a noob question: Did you try refreshing pgadmin itself?
@Noumenon For me, closing and opening the pgadmin helped.
|
89

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

Improve this answer

7 Comments

bonus point for being the only answer to provide the SHOW part
Roger that, Ariel Allon -- and, for the next guy who wants to select that time zone into a variable... SELECT current_setting('TIMEZONE')
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.
@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.
@MikkoRantalainen I have no idea what is “default timezone for the database”. Please provide a link to documentation.
|
64

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

Improve this answer

8 Comments

This is actually the correct answer as it makes the TZ change default for every process in PGSQL, not just current user.
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).
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.
I don't have that folder in share postgresql
Instead of restart PostgreSQL, you can run select pg_reload_conf().
|
12

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)

Postgres config in pgAdmin III

Improve this answer

1 Comment

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
10

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
Improve this answer

1 Comment

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

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.

Improve this answer

3 Comments

so this needs to be kept in sync with the summer/winter time changes?
@Cpt.Senkfuss, I believe something like -Duser.timezone=Australia/Tasmania should also work and take care of summer\winter changes.
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
2

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?

Improve this answer

Comments

1

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)

Improve this answer

Comments

0

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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.