How to add an unsupported time zone to the Greenplum Database?

posted Apr 28, 2017, 4:56 PM by Sachchida Ojha
Some time zones that the host operating system supports are not supported bythe Greenplum Database.
For example, if the OS time zone is "Asia/Jakarta," the corresponding time zone abbreviation "WIT" is not supported in the Greenplum Database.

export TZ=Asia/Jakarta

gpadmin=# show timezone;
(1 row)

gpadmin=# select * from pg_timezone_names where name='Asia/Jakarta';
     name     | abbrev | utc_offset | is_dst
 Asia/Jakarta | WIT    | 07:00:00   | f
(1 row)

gpadmin=# create table t1(id timestamp with time zone);
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table.
HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew.

gpadmin=# insert into t1 values('2011-11-23 10:33:48.888539 WIT');
ERROR:  invalid input syntax for type timestamp with time zone: "2011-11-23 10:33:48.888539 WIT"

Root Cause:

If Greenplum is using an unsupported time zone such as "Asia/Jakarta" and its abbreviation "WIT," any Greenplum internal operation that inserts into gp_configuration_history triggers errors since gp_configuration_history has a column "time" with type"timestamp with time zone."

For example, gprecoverseg may be affected because it will modify gp_segment_configuration and also gp_configuration_history. The modification of the two tables is in the same transaction so if the operation on gp_configuration_history fails, the whole transaction will fail and rollback. As a result, the Greenplum configuration will in an unhealthy status.


Follow these steps:

  1. Calculate the offset seconds based on the time zone. For example, "Asia/Jakarta" is for GMT+7, so offset seconds = 7 * 3600 seconds = 25200 seconds.
  2. On master, add "<time zone abbreviation>  <offset seconds>" into $GPHOME/share/postgresql/timezonesets/Default. For example, add "WIT 25200" into $GPHOME/share/postgresql/timezonesets/Default for "Asia/Jakarta."
  3. Restart the GP cluster.


[gpadmin@mdw]$ echo "WIT 25200" > $GPHOME/share/postgresql/timezonesets/Default

gpstop -r

gpadmin=# select * from pg_timezone_abbrevs where abbrev='WIT';
 abbrev | utc_offset | is_dst
 WIT    | 07:00:00   | f
(1 row)

gpadmin=# insert into t1 values('2011-11-23 10:33:48.888539 WIT');
Time: 70.492 ms