Show timezone in PostgreSQL Database Server

Posted on

Sometime, there is a certain need to check the timezone of a running PostgreSQL Database Server.  The value of timezone configured or set in a running PostgreSQL Database Server will determine the value of the query retrieved by a person, a database administrator, applications even systems which is asking the current time accordingly to PostgreSQL Database Server as shown below :

select now()

When the above query executed in PostgreSQL Database Server as shown below :

postgres=# select now();
now
-------------------------------
2016-11-22 19:58:02.282261+07
(1 row)

postgres=#

The above query is becoming an important thing in several occasions for example an application or systems built with a logging feature as an audit trail mechanism. It will log for an example user activities such as login, logout, execute something and it will be saved in the table with the information of the time it is being done. Image if the base time which is retrieved from timezone is false then the feature which is made for such purpose will be meaningless. It is obviously because the time configured or set is false. So, how to see the current timezone value of a running PostgreSQL Database Server?, below is the query executed to demonstrate how to do it.

show timezone
postgres=# show timezone;
TimeZone
------------
US/Eastern (1 row)

Where does the value came from?, one of the reason the above value is given is from the configured PostgreSQL Database configuration file which is stored in postgresql.conf. The file itself is located in the data folder of PostgreSQL Database Server defined in the PostgreSQL Datbase Server’s installation process.

If the file is opened below is the definition of timezone configured :

timezone = 'US/Eastern'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
# abbreviations.  Currently, there are
#   Default
#   Australia (historical usage)
#   India
# You can create your own file in
# share/timezonesets/.

To be able to change the timezone, just change the value of the timezone field above and don’t forget to save the file and restart the PostgreSQL Database Server’s service.

One thought on “Show timezone in PostgreSQL Database Server

Leave a Reply