Adding or Modifying Time Zone Strings - Advanced SQL Engine - Teradata Database

SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
xmd1556127764262.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1211
lifecycle
previous
Product Category
Teradata Vantage™

Teradata Database provides a set of time zone strings that represent commonly used time zones. For a list of supported time zone strings, see AT LOCAL and AT TIME ZONE Time Zone Specifiers. The GetTimeZoneDisplacement UDF stores and maintains these time zone strings and the related rules for converting between UTC and the time in the local time zone.

If the supplied time zone strings do not meet your requirements, you may add or modify the time zone strings by modifying the GetTimeZoneDisplacement UDF, which is located in the SYSLIB database. The source code for the UDF is available as part of the DBS package and is located at /tdbms/etc/dem/src.

To define new time zone strings or add or modify the rules of an existing time zone string:

  1. Make a backup copy of the existing GetTimeZoneDisplacement UDF.
    # cd /home
    # mkdir workdir
    # cd workdir
    # cp /tdbms/etc/dem/src/udfgettimezonedisplacement.c .
    
  2. To modify an existing time zone string:
    Please engage Teradata Customer Support Services to consult about any rule change in existing time zone strings provided in tdlocaledef_tzrules.txt.
    # cd /opt/teradata/tdat/tdbms/xx.xx.xx.xx/etc
    # vi tdlocaledef_tzrules.txt
    
    TimeZoneString {"America Eastern"; "-5"; "0"; "2";
         "4"; "4"; "1"; "0"; "0"; "02:00:00";
         "3"; "10"; "0"; "0"; "-1"; "02:00:00";
         "1987"; "2006"; "-5"; "0"; "-4"; "0";
              "4"; "3"; "8"; "0"; "0"; "02:00:00";
              "4"; "11"; "1"; "0"; "0"; "02:00:00";
              "2007"; "9999"; "-5"; "0"; "-4"; "0"}
    
    1. Find the time zone string entry in the TZ_DST structure of the GetTimeZoneDisplacement UDF. For example:
      {"America Eastern", 2,
        {{{4,  4, 1, 0, 0, "02:00:00"},
           {3, 10, 0, 0, -1, "02:00:00"},
           {1987, 2006, -5, 0, -4, 0}},
          {{4,  3, 8, 0, 0, "02:00:00"},
           {4, 11, 1, 0, 0, "02:00:00"},
           {2007, 9999, -5, 0, -4, 0}},
          {{0, 0, 0, 0, 0, "00:00:00"},
           {0, 0, 0, 0, 0, "00:00:00"},
           {0, 0, 0, 0, 0, 0}},
          {{0, 0, 0, 0, 0, "00:00:00"},
           {0, 0, 0, 0, 0, "00:00:00"},
           {0, 0, 0, 0, 0, 0}},
          {{0, 0, 0, 0, 0, "00:00:00"},
           {0, 0, 0, 0, 0, "00:00:00"},
           {0, 0, 0, 0, 0, 0}},
          {{0, 0, 0, 0, 0, "00:00:00"},
           {0, 0, 0, 0, 0, "00:00:00"},
           {0, 0, 0, 0, 0, 0}}
        },
        -5, 0
       },
      Rules based on a time zone string in tdlocaledef_tzrules.txt and udfgettimezonedisplacement.c are listed in different formats. To run tdlocaledef to set the time zone string for a system, the format for tdlocaledef_tzrules.txt is used.
    2. Modify the rules and information associated with the time zone string entry or add new rules to the entry.
  3. To add a new time zone string:
    1. Create a new entry in the TZ_DST structure for the new time zone string and its related rules.
      Rules based on a time zone string in tdlocaledef_tzrules.txt and udfgettimezonedisplacement.c are listed in different formats. To run tdlocaledef to set the time zone string for a system, the format for tdlocaledef_tzrules.txt is used.
    2. Place the new time zone string entry in the correct alphabetical position within the TZ_DST structure.
    3. MAX_DST_ENTRIES in udfgettimezonedisplacement.c must match the existing time zone strings. Increase one when adding one new time zone string.
  4. Recompile the UDF using the REPLACE FUNCTION statement. For more information, see “CREATE FUNCTION (External Form)/ REPLACE FUNCTION (External Form)” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
    Make sure to log in as DBC and that no other user is logged on.

    For example:

    Database SYSLIB;
    DROP FUNCTION GetTimeZoneDisplacement;
    REPLACE FUNCTION GetTimeZoneDisplacement
                     (tzstringinfo VARBYTE(130))
    RETURNS BYTE(340)
    LANGUAGE C
    NO SQL
    PARAMETER STYLE SQL
    EXTERNAL NAME 'CS!GetTimeZoneDisplacement!/home/workdir/udfgettimezonedisplacement.c' ; 
    
  5. Test the new time zone string:
    .os date
    .os date -u
    
    SELECT CURRENT_TIMESTAMP AS SYSTEMTIME,
           CURRENT_TIMESTAMP AT 'new_time_zone_string';