How to configure postgres Timezone?
In PostgreSQL, the timezone configuration is essential for handling date and time values accurately, especially when dealing with data from different time zones. The system time zone of the operating system can also influence PostgreSQL's behaviour since, PostgreSQL queries the system for the local time zone if not explicitly configured.
If you want to explicitly configure timezone for the PostgreSQL server, We can set the timezone either of the below methods in appropriate cases.
Pro-tip :
you can find your appropriate timezone value from the list of time zones by executing the below query.
- SELECT * FROM pg_timezone_names;
Check current Timezone by
- SHOW TIMEZONE;
Case 1 : Configuring Timezone for the PostgreSQL server globally, persistent over multiple sessions.
- Navigate to <product_installation_directory> \ pgsql \ ext_conf folder.
- Add/Edit the below entry in 00framework_ext.conf file.
timezone='Asia/Kolkata'
log_timezone='Asia/Kolkata'
3. Restart the PostgreSQL server.
Case 2 : Configuring timezone only to specific databases, persistent over multiple sessions.
- Login into PostgreSQL server in command prompt
- Execute the below SQL query.
ALTER DATABASE servicedesk SET TIMEZONE TO 'America/New_York
3. Restart the PostgreSQL server.
Case 3 : Testing timezone configuration, (persist only in current session)
- Login into PostgreSQL server in command prompt.
- Execute the below SQL query.
SET TIMEZONE to 'America/New_York';
This case, will be useful for testing the appropriate timezone before configuring your database to switch to that timezone.
If you face any issues in configuring the Timezone, Reach our Support Team with the below information.
- Operating system
- Steps followed to set the Timezone.
- PostgreSQL configuration files such as,
- <installation_folder> \ pgsql \ data \ postgresql.conf
- <installation_folder> \ pgsql \ ext_conf folder
- <installation_folder> \ ext_conf \ 00framework_ext.conf file
- PostgreSQL logs generated when setting the timezone, which can be found under " <installation_folder> \ pgsql \ data \ pg_log folder"
New to ADSelfService Plus?
Related Articles
How to Connect External Postgres
Install a External Postgres such that the version of that postgres is currently in the series of inbuilt postgres of our Application. Say for example, In Servicedeskplus Application version 14010, which has postgres version 10.21, then you should ...Remote read only access to database for Postgres customers
Frequently customers want to connect some reporting / dashboard application like PowerBI or Tableau with our Postgres database server. By default, the bundled Postgres is configured to only listen to the local machine. We can configure to allow ...Query Executor Tool for PostGres
This tool is designed to execute queries in the customer environment by connecting the database by reading the database configuration file. We need to enter the query that we require to execute in queryToExecute.txt file. We can enter multiple ...Migration failure for Postgres Database with a blank space in it
Trace : \ManageEngine\ServiceDesk\Patch\AdventNet_ManageEngine_ServiceDesk_Plus-14.2.0-SP-1.0.0\SERVICEDESK\PreInstall\pg_migrate\pgsql_old\bin\pg_dumpall.exe", -U, postgres, -p, 65433, -h, 127.0.0.1, -r, -w, |, ...su: cannot open session: Module is unknown
The upgrade issue arises in several linux editions such as RHEL and CentOS when the bundled postgres database needs to be upgraded in that particular patch and the upgrade is triggered by the root user. The highlighted line in the traces below (su: ...