postgres default timezone

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!

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.

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.

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

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

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)