How to change the time zone on the Pivotal Greenplum Database

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article explains how to change the timezone on the GPDB and the database logs (located under $MASTER_DATA_DIRECTORY/pg_log).

 

Environment

Resolution

Two database parameters (GUC's) are used to control the behavior of the time in the database.
 

1. timezone - Reflects the time on the database.
2. log_timezone - Reflects the time on the database logs.

The location (indicated below) lists all the timezones that can be used:

/usr/local/greeplum-db/share/postgresql/timezone 

Example

1. Execute the following query to obtain the current time:

flightdata=# select now();
              now
-------------------------------
 2014-11-21 07:16:03.187754-08
(1 row)

flightdata=# set timezone='Europe/Vienna';
SET
flightdata=# select now();
			now
-------------------------------
 2014-11-21 16:16:07.883515+01
(1 row) 

2. To change to a time zone in the continent of Africa, go to the Africa folder at "/usr/local/greeplum-db/share/postgresql/timezone". Consider changing the the time zone to match the location of "Windhoek":

[gpadmin@mdw timezone]$ cd Africa/
[gpadmin@mdw Africa]$ ls -ltr | grep Windhoek
total 220
-rwxr-xr-x 1 gpadmin gpadmin 1556 Aug 26 16:49 Windhoek 

3. To set the time zone on the database, execute the following query: 

flightdata=# set timezone='Africa/Windhoek' ;
SET

flightdata=# select now();
              now
-------------------------------
 2014-11-21 17:19:57.438173+02
(1 row) 

4. To make these changes permanent, use the following command:

gpconfig --skipvalidation -c timezone -v \'Africa/Windhoek\' -m \'Africa/Windhoek\' 

5. Reload the configuration.

Note: Similarly, to change the timezone of the database logs, edit the parameter "log_timezone".