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

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)

Teradata 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 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 details, see the information about the external form of REPLACE FUNCTION 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';