
Vertica recognizes many time zones. However, there might come a time (zone) when you will need to reference one that is not available by default. Luckily it’s relatively easy to add a time zone to Vertica.
Example:
The “Hawaii Standard Time (HST)” is a default time zone available in Vertica.
dbadmin=> SELECT now() AT TIME ZONE 'GMT' "Greenwich Mean Time",
dbadmin-> now() AT TIME ZONE 'HST' "Hawaii Standard Time";
Greenwich Mean Time | Hawaii Standard Time
----------------------------+----------------------------
2018-02-06 14:34:50.771044 | 2018-02-06 04:34:50.771044
(1 row)
But I would like to use the “Hawaii-Aleutian Daylight Time (HDT)” time zone, which unfortunately is not available by default.
dbadmin=> SELECT now() AT TIME ZONE 'GMT' "Greenwich Mean Time",
dbadmin-> now() AT TIME ZONE 'HST' "Hawaii Standard Time",
dbadmin-> now() AT TIME ZONE 'HDT' "Hawaii Daylight Saving Time";
ERROR 5038: Timezone "HDT" not recognized
To add the HDT time zone I’ll need to do a little work. On each node, I’ll add the following text to the /opt/vertica/share/timezonesets/Default file:
HDT -32400 D # Hawaii-Aleutian Daylight Time
Once the database is restarted, the HDT time zone will be available for use!
dbadmin=> SELECT now() AT TIME ZONE 'GMT' "Greenwich Mean Time",
dbadmin-> now() AT TIME ZONE 'HST' "Hawaii Standard Time",
dbadmin-> now() AT TIME ZONE 'HDT' "Hawaii Daylight Saving Time";
Greenwich Mean Time | Hawaii Standard Time | Hawaii Daylight Saving Time
--------------------------+--------------------------+-----------------------------
2018-02-06 14:36:53.2233 | 2018-02-06 04:36:53.2233 | 2018-02-06 05:36:53.2233
(1 row)
Have fun!