Set Timezone in PostgreSQL using Query

Having configured an operating system with the correct timezone, in this case in Linux operating system doesn’t guarantee an installed PostgreSQL Database Server will have the same timezone.

For an example, an installed CentOS operating system may show its time zone is America/Denver but the installed PostgreSQL Database Server on it shows the value of its time zone is Europe/Dublin for instance or based on experience it is set in the default time zone US/Eastern.

To change the timezone for PostgreSQL Database Server, it can be done by executing query in PostgreSQL Command Console.

First of all, connect to PostgreSQL Command Console as shown below :

psql -U username

[root@hostname ~]# psql -U postgres 
psql (9.4.5)
Type "help" for help.
postgres=#

After that, type the following query to show timezone in PostgreSQL Database Server :

show timezone

Below is the execution of the command in PostgreSQL Command Console :

postgres=# show timezone;
TimeZone
--------------
Australia/Sydney
(1 row)
postgres=# quit

Actually the timezone has already defined before. It can be set with another value or in other word it can be reset. Below is how to set or to reset it :

Below is the query executed to change the time zone :

set timezone='TimeZone';
Description : 
set : It is the command used to define a variable in PostgreSQL Command Console
timezone : The field or variable which its value is going to be defined or redefined 
'TimeZone' : It is the timezone string character. It can be retrieved by executing query 'select * from pg_timezone_names;'

Below is the execution of the query in PostgreSQL Command Console :

postgres=# set timezone='US/Eastern';
SET
postgres=# show timezone;
TimeZone
------------
US/Eastern
(1 row)
postgres=#
root@hostname:/etc/postgresql/9.3/main#

After proving that the command for setting timezone above is effective, it is actually doesn’t change after disconnecting from PostgreSQL Command Console and then reconnecting back as shown below :

root@hostname:/etc/postgresql/9.3/main# psql -Upostgres
Password for user postgres:
psql (9.5.3, server 9.3.10)
Type "help" for help.
postgres=# show timezone;
TimeZone
-----------
Australia/Sydney
(1 row)
postgres=#

Based on the above output sequence, after changing the timezone from Australia/Sydney into US/Eastern in PostgreSQL Command Console, the value is returning back after disconnect and reconnect back. To be able to change or to set if permanently, change it from PostgreSQL configuration file. It can be shown in the article title ‘Show timezone in PostgreSQL Database Server‘. After changing it, don’t forget to restart PostgreSQL Database Server.