Adding or Modifying Time Zone Strings - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
Product Category
Teradata Vantage™

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 Support 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';