Vertica Quick Tip: Add a Time Zone

This blog post was authored by Jim Knicely.

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!