GST timezone is not recognized by vertica

Deepak_Nair · April 2014

I am trying to convert string to timezone and in the end convert it to PST. I am getting the following error
Timezone "GST" not recognized

when I use the following sql

to_timestamp(trim(lower(TIME_STRING)),'YYYY-MM-DD HH24:MI:SS TZ')

where TZ is GST

Deepak_Nair · April 2014

List of timezones in the data is matching to
http://en.wikipedia.org/wiki/List_of_time_zone_abbreviations

I have the same problem with SAMT

[Deleted User] · April 2014

Hi,

Vertica uses a set of files under the /opt/vertica/share/timezonesets dir as a database of timezone abbreviations and names. See the "Date/Time Data Types" page of the SQL Guide for a small note with details and the READMe in that dir.

There's a file named "Default" which has a pointer to a file named "WorldWide". That WorldWide file has the list of timezones Vertica supports by default. There are other files in that dir such as Asia.txt with additional timezones. These can be pointed to as overrides or used as a source to copy/paste into a custom file or the Default file. In my test I copied the GST info from the Asia.txt file into the WorldWide file as below, restarted the db, and GST then was usable.

dbadmin=> select to_timestamp_tz(trim(lower('2014-04-30 14:20:00 GST')),'YYYY-MM-DD HH24:MI:SS TZ');
ERROR 2005: Timezone "gst" not recognized

GFT    -10800    # French Guiana Time
                 #     (America/Cayenne)
GST     14400    # Gulf Standard Time
                 #     (Asia/Dubai)
                 #     (Asia/Muscat)
GYT    -14400    # Guyana Time
                 #     (America/Guyana)

test7=> select to_timestamp_tz(trim(lower('2014-04-30 14:20:00 GST')),'YYYY-MM-DD HH24:MI:SS TZ');
    to_timestamp_tz
------------------------
2014-04-30 10:20:00+00
(1 row)

SAMT is not in the WorldWide but is in the Europe.txt, so it could be manipulated the same way.
[dbadmin@scrossman7 timezonesets]$ grep -rl "SAMT" *
Europe.txt

Note that on upgrade these changes are most likely going to get overwritten, so you'd need to reapply the changes.

Deepak_Nair · April 2014

Would I need to do this on all the Vertica nodes.... How do I keep this persistent across upgrades

[Deleted User] · April 2014

Yes, I believe it would have to be on all nodes as any could be an initiator and any could potentially participate. You would have to work the mods into your upgrade steps, possibly do this change after the update_vertica and before restarting the db. If you take the WorldWide file and do mods to it and save it off somewhere, then you can propagate it after the update without having to edit any files. You'd also need to consider this process any time you replace or add a node.

Deepak_Nair · May 2014

Modifying the worldwide and bringing the database down led to the db being down. This solution did not help me is there a place where I can find an updated WorldWide file that has been tested with all timezones

Added the following entries to the worldwide

#Added timezones
CAT 7200 # Central Africa Time
# (Africa/Blantyre)
# (Africa/Bujumbura)
# (Africa/Gaborone)
# (Africa/Harare)
# (Africa/Kigali)
# (Africa/Lubumbashi)
# (Africa/Lusaka)
# (Africa/Maputo)
ADT -10800 D # Atlantic Daylight Time
# (America/Glace_Bay)
# (America/Goose_Bay)
# (America/Halifax)
# (America/Thule)
# (Atlantic/Bermuda)

AMST -10800 D # Amazon Summer Time
# (America/Campo_Grande)
# (America/Cuiaba)
AMT -14400 # Amazon Time
# (America/Boa_Vista)
# (America/Campo_Grande)
# (America/Cuiaba)
# (America/Manaus)
# (America/Porto_Velho)
AST -14400 # Atlantic Standard Time
# (America/Anguilla)
# (America/Antigua)
# (America/Aruba)
# (America/Curacao)
# (America/Dominica)
# (America/Glace_Bay)
# (America/Goose_Bay)
# (America/Grenada)
# (America/Guadeloupe)
# (America/Halifax)
# (America/Martinique)
# (America/Montserrat)
# (America/Port_of_Spain)
# (America/Puerto_Rico)
# (America/Santo_Domingo)
# (America/St_Kitts)
# (America/St_Lucia)
# (America/St_Thomas)
# (America/St_Vincent)
# (America/Thule)
# (America/Tortola)
# (Atlantic/Bermuda)
ECT -18000 # Ecuador Time
# Eastern Caribbean Time
# (America/Guayaquil)
GMT 0 # Greenwich Mean Time
# (Africa/Abidjan)
# (Africa/Bamako)
# (Africa/Banjul)
# (Africa/Bissau)
# (Africa/Conakry)
# (Africa/Dakar)
# (Africa/Lome)
# (Africa/Monrovia)
# (Africa/Nouakchott)
# (Africa/Ouagadougou)
# (Africa/Sao_Tome)
# (America/Danmarkshavn)
# (Atlantic/Reykjavik)
# (Atlantic/St_Helena)
# (Etc/GMT)
# (Europe/Dublin)
# (Europe/London)
HADT -32400 D # Hawaii-Aleutain Daylight Time
# (America/Adak)
HAST -36000 # Hawaii-Aleutain Standard Time
# (America/Adak)
SRT -10800 # Suriname Time
# (America/Paramaribo)
ADT 14400 D # Arabia Daylight Time
# (Asia/Baghdad)
AQTT 18000 # Aqtau Time
# Aqtobe Time
# (Asia/Aqtau)
# (Asia/Aqtobe)
AST 10800 # Arabia Standard Time
# (Asia/Aden)
# (Asia/Baghdad)
# (Asia/Bahrain)
# (Asia/Kuwait)
# (Asia/Qatar)
# (Asia/Riyadh)
CHOST 36000 D # Choibalsan Summer Time
# (Asia/Choibalsan)
CHOT 32400 # Choibalsan Time
# (Asia/Choibalsan)
CIT 28800 # Central Indonesia Time
# (Asia/Makassar)
EEST 10800 D # East-Egypt Summer Time
# Eastern Europe Summer Time
# (Africa/Cairo)
# (Asia/Amman)
# (Asia/Beirut)
# (Asia/Damascus)
# (Asia/Gaza)
# (Asia/Nicosia)
# (Europe/Athens)
# (Europe/Bucharest)
# (Europe/Chisinau)
# (Europe/Helsinki)
# (Europe/Istanbul)
# (Europe/Kaliningrad)
# (Europe/Kiev)
# (Europe/Minsk)
# (Europe/Riga)
# (Europe/Simferopol)
# (Europe/Sofia)
# (Europe/Tallinn)
# (Europe/Uzhgorod)
# (Europe/Vilnius)
# (Europe/Zaporozhye)
EET 7200 # East-Egypt Time
# Eastern Europe Time
# (Africa/Cairo)
# (Africa/Tripoli)
# (Asia/Amman)
# (Asia/Beirut)
# (Asia/Damascus)
# (Asia/Gaza)
# (Asia/Nicosia)
# (Europe/Athens)
# (Europe/Bucharest)
# (Europe/Chisinau)
# (Europe/Helsinki)
# (Europe/Istanbul)
# (Europe/Kaliningrad)
# (Europe/Kiev)
# (Europe/Minsk)
# (Europe/Riga)
# (Europe/Simferopol)
# (Europe/Sofia)
# (Europe/Tallinn)
# (Europe/Uzhgorod)
# (Europe/Vilnius)
# (Europe/Zaporozhye)
EIT 32400 # East Indonesia Time
# (Asia/Jayapura)
GST 14400 # Gulf Standard Time
# (Asia/Dubai)
# (Asia/Muscat)
HOVST 28800 D # Hovd Summer Time
# (Asia/Hovd)
HOVT 25200 # Hovd Time
# (Asia/Hovd)
IRDT 16200 D # Iran Daylight Time
# (Asia/Tehran)
IRST 12600 # Iran Standard Time
# (Asia/Tehran)
IST 19800 # Indian Standard Time
# (Asia/Calcutta)
ORAT 18000 # Oral Time
# (Asia/Oral)
QYZT 21600 # Kizilorda Time
# (Asia/Qyzylorda)
SAKST 39600 D # Sakhalin Summer Time
# (Asia/Sakhalin)
SAKT 36000 # Sakhalin Time
# (Asia/Sakhalin)
TLT 32400 # East Timor Time
# (Asia/Dili)
IT 25200 # Waktu Indonesia Timur
# West Indonesia Time
# (Asia/Jakarta)
# (Asia/Pontianak)
SAMST 18000 D # Samara Summer Time
# (Europe/Samara)
SAMT 14400 # Samara Time
# (Europe/Samara)
WEST 3600 D # Western Europe Summer Time
# (Africa/Casablanca)
# (Atlantic/Canary)
# (Atlantic/Faeroe)
# (Atlantic/Madeira)
# (Europe/Lisbon)
ChST 36000 # Chamorro Standard Time (lower case "h" is as in zic)
# (Pacific/Guam)
# (Pacific/Saipan)
PST -28800 # Pacific Standard Time
# (America/Dawson)
# (America/Los_Angeles)
# (America/Tijuana)
# (America/Vancouver)
# (America/Whitehorse)
# (Pacific/Pitcairn)
SBT 39600 # Solomon Islands Time
# (Pacific/Guadalcanal)
SST -39600 # South Sumatran Time
# (Pacific/Midway)
# (Pacific/Pago_Pago)

[Deleted User] · May 2014

Deepak,

As noted, the WordWide file is the default set and any additional ones you want you need to add entries from the other files to it or create a custom file for the default to point to that has the desired ones. There is no distributed file with all timezones, mostly due to conflicts where there are multiple meanings for the same 3 letter acronym (e.g. IST is both India Std Time and Israel Std Time). I have successfully added the GST and SAMT you noted you needed to the WorldWide without any db restart issues and they worked when used in the timestamp_tz function.

I added GST as below in the Asia section as below:
GET     10800    # Georgia Time (there was a timezone change in 2004)
                 #     (Asia/Tbilisi)
GST     14400    # Gulf Standard Time
                 #     (Asia/Dubai)
                 #     (Asia/Muscat)
HKT     28800    # Hong Kong Time (not in zic)

I added SAM and SAMT in the Europe section as below:
MSK     10800    # Moscow Time
                 #     (Europe/Moscow)
SAMST   18000 D # Samara Summer Time
                 #     (Europe/Samara)
SAMT    14400    # Samara Time
                 #     (Europe/Samara)
WET         0    # Western Europe Time
                 #     (Africa/Casablanca)
                 #     (Africa/El_Aaiun)
                 #     (Atlantic/Canary)
                 #     (Atlantic/Faeroe)
                 #     (Atlantic/Madeira)
                 #     (Europe/Lisbon)

Did you possibly edit the file with an editor that added CTRL Ms to line ends or otherwise made the file non Linux text format? Vertica doesn't seem to be picky about location of the entries but may be picky about stray extra blank lines or odd line ends or possibly even number of entries.

GST timezone is not recognized by vertica

Comments

Leave a Comment