17.00 - 17.05 - Example: Adding a New Time Zone String - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
June 2020
Content Type
Programming Reference
Publication ID
B035-1211-170K
Language
English (United States)
Assume that you want to add a new time zone string 'Europe Azores', which has one rule with the following time zone displacement information:
  • DST starts on the last Sunday in March at 12:00 am local time.
  • DST ends on the last Sunday in October at 1:00 am local time.
  • The standard time zone offset from UTC is -1.
  • The daylight saving time offset from UTC is 0.
  • The start year for the rule is 2009.
  • The end year for the rule is 2010.

Based on this information, the new time zone string entry for 'Europe Azores' is:

{"Europe Azores", 1,                         <= 1 rule defined for 'Europe Azores'
  {{{3,  3, 0, 0, -1, "00:00:00"},           <= Start of rule 1, startDST information
    {3, 10, 0, 0, -1, "01:00:00"},           <= endDST information
    {2009, 2010, -1, 0, 0, 0}},              <= validyrs information
   {{0, 0, 0, 0, 0, "00:00:00"},             <= Start of rule 2
    {0, 0, 0, 0, 0, "00:00:00"},
    {0, 0, 0, 0, 0, 0}},
   {{0, 0, 0, 0, 0, "00:00:00"},             <= Start of rule 3
    {0, 0, 0, 0, 0, "00:00:00"},
    {0, 0, 0, 0, 0, 0}},
   {{0, 0, 0, 0, 0, "00:00:00"},             <= Start of rule 4
    {0, 0, 0, 0, 0, "00:00:00"},
    {0, 0, 0, 0, 0, 0}},
   {{0, 0, 0, 0, 0, "00:00:00"},             <= Start of rule 5
    {0, 0, 0, 0, 0, "00:00:00"},
    {0, 0, 0, 0, 0, 0}},
   {{0, 0, 0, 0, 0, "00:00:00"},             <= Start of rule 6
    {0, 0, 0, 0, 0, "00:00:00"},
    {0, 0, 0, 0, 0, 0}}
  },
  -1, 0                                      <= Standard time zone displacement
},

Note that the time zone string entry has space for 6 rules but only one rule is used for the start year 2009 and end year 2010.

You must place the new 'Europe Azores' time zone string in between the 'Australia Western' and 'Europe Central' time zone strings in the TZ_DST structure to maintain the alphabetical order of the structure.