GST timezone is not recognized by vertica

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

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.

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

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.

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)

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.