15.00 - GetTimeZoneDisplacement - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

GetTimeZoneDisplacement

Purpose  

Returns the rules and time zone displacement information for a specified time zone string.

Syntax  

where:

 

Syntax element …

Specifies …

time_zone_string

a valid time zone string specified using a VARBYTE data type. For a list of time zone strings supported by Teradata, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

If time_zone_string is not valid or unsupported, GetTimeZoneDisplacement returns a value of 1 in the first byte to indicate that the time zone string does not exist.

ANSI Compliance

This is a Teradata extension to the ANSI SQL:2011 standard.

Result

GetTimeZoneDisplacement returns a string of bytes containing the rules and time zone displacement information for the specified time zone string. The result data type is BYTE. The information returned is:

 

Byte

Value

First byte

  • 1, if the time zone string is not found. That is, the time zone string specified in the input argument is not valid or unsupported.
  • 0, if the time zone string is found.
  • Second byte

  • 1, if the time zone string has separate daylight saving time and standard time zone displacements from Coordinated Universal Time (UTC) time.
  • In this case, the next 480 or so bytes store the set of rules describing a valid standard time zone displacement, daylight saving time zone displacement, and the start and end time for daylight saving time. A maximum of 6 rules are stored for each time zone string.

  • 0, if the time zone string does not have separate daylight saving time and standard time zone displacements from UTC time.
  • In this case, the next 4 bytes store the time zone displacement hour and minute values.

    Usage Notes

    GetTimeZoneDisplacement is a system user-defined function (UDF) that Teradata Database invokes internally to resolve a time zone string specified in an SQL statement or Specification for Data Formatting (SDF) file. You do not invoke this function directly; however, you can modify this UDF to add new time zone strings or add or modify the rules of an existing time zone string.

    Limitation on the Use of TimeZone Strings

    When using standard Teradata Database system time zone strings, no time zone rules are enforced for the years 1986 and before, for example, daylight saving time (DST) shifts. Valid years for Teradata Database standard time zone strings are 1987 through 9999.

    If Teradata Database standard time zone strings do not meet your requirements, you can add a new custom time zone string or modify an existing string by modifying or adding new rules to GetTimeZoneDisplacement.

    Note: When time zone strings are used for the locales that observe DST, but time zone offset values are not included with time values, Teradata Database cannot determine whether time values within this ambiguous hour should be interpreted and treated as DST or as standard time. In these cases, the DBS Control setting for DisplacementOnOverlap determines whether these times are assumed by Teradata Database to have a DST or standard time zone offset.

    This issue occurs only when time zone strings are used for DST locales, when time values do not include a time zone offset value, and when the DBS Control setting for TimeDateWZControl is set to 3 (date and time values without time zone offset information are stored using system local time).

    For more information on DisplacementOnOverlap TimeDateWZControl, see the DBS Control chapter in Utilities: Volume 1 (A-K).

    Adding or Modifying Time Zone Strings

    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” on page 827. 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:

    Note: 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"}

    a 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
     },

    Note: 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.

    b 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:

    a Create a new entry in the TZ_DST structure for the new time zone string and its related rules.

    b Place the new time zone string entry in the correct alphabetical position within the TZ_DST structure.

    c 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 SQL Data Definition Language. For example:

    Note: Make sure to login 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';

    The TZ_DST Structure

    The TZ_DST structure is an array of TZwithDST elements where each element describes a time zone string and its related rules. The definition of the TZwithDST structure is:

       typedef struct TZwithDST
       {
           CHARACTER_LATIN  tzstring[TZSTRINGSIZE];
           int              number_of_rules;
           DSTRules         TZRules[TZRulesEntries];
           SMALLINT         Standardtzdispl_hour; 
           SMALLINT         Standardtzdispl_minute; 
       } TZwithDST;

    where:

     

    Field

    Description

    tzstring

    The name of the time zone string. For example, "America Pacific."

    The maximum length of a time zone string is 130 bytes.

    number_of_rules

    The number of rules related to this time zone string. A maximum of 6 rules is allowed for each time zone string.

    TZRules

    An array where each DSTRules element describes a rule. These rules are used to calculate the time zone displacement for the time zone string.

    Standardtzdispl_hour

    The standard time zone displacement hour.

    Standardtzdispl_minute

    The standard time zone displacement minute.

    Each DSTRules element of the TZRules array describes a rule for the time zone string. The definition of the DSTRules structure is:

       typedef struct DSTRules
       {
           startendDSTInfo  startDST;
           startendDSTInfo  endDST;
           yearDisplInfo    validyrs;
       } DSTRules;

    where:

     

    Field

    Description

    startDST

    Specifies the date and time when daylight saving time (DST) starts.

    endDST

    Specifies the date and time when daylight saving time ends.

    validyrs

    Specifies the years in which the DST start and end dates apply. The following information related to this year range is included:

  • start_year - the year when these DST rules start.
  • end_year - the year when these DST rules end.
  • Standardtzdispl_hour - the standard time zone displacement hour.
  • Standardtzdispl_minute - the standard time zone displacement minute.
  • DSTtzdispl_hour - the time zone displacement hour for daylight saving time.
  • DSTtzdispl_minute -the time zone displacement minute for daylight saving time.
  • You can specify the following for startDST and endDST. Enter zero if a field is not applicable.

     

    Field

    Description

    rule_type

    Indicates how the start and end date for DST is specified. The valid values are:

  • 0 - No DST start or end information is specified. The standard time zone displacement is used.
  • 1 - DST starts or ends on the specified fixed date. The date is specified by the month and day_of_month fields.
  • 2 - DST starts or ends on the 1st, 2nd, or 3rd weekday of the month as indicated by the month, day_of_week, and week_of_month fields.
  • 3 - DST starts or ends on the 2nd to the last, 3rd to the last, or the last weekday of the month as indicated by the month, day_of_week, and week_of_month fields.
  • 4 - DST starts or ends on the next weekday on or immediately after the date specified in the day_of_month field. The month and weekday are specified in the month and day_of_week fields.
  • For example, for time zone string 'America Pacific', the start date rule is the first Sunday after March 8th, which gives us March 14th for the year 2010.

    month

    The month when DST starts or ends. Valid values are 0- 12. This field is used for rule_type 1, 2, 3, and 4.

    For example, for time zone string 'America Pacific', the start date rule is the first Sunday after March 8; therefore, this field has a value of 3 in the startDST structure to represent March.

    day_of_month

    If rule_type is 1, this field specifies the day of the month when DST starts or ends. For example, if DST ends at 12:00 am local time on August 21, this field contains the value 21 in the endDST structure.

    If rule_type is 4, DST starts or ends on the next weekday on or immediately after the date specified by this field. For example, for time zone string 'America Pacific', the start date rule is the first Sunday after March 8; therefore, this field has a value of 8 in the startDST structure.

    When rule_type is 0, 2 or 3, this field is not used and the value is 0.

    day_of_week

    The valid values are 0-7 representing the weekdays Sunday-Saturday. This field is used for rule_type 2, 3 and 4.

    For example, for time zone string 'America Pacific', the start date rule is the first Sunday after March 8; therefore, this field has a value of 0 in the startDST structure to represent Sunday.

    week_of_month

    The valid values are 1, 2, 3, 4, 5, -1, and -2 representing the 1st, 2nd, 3rd, 4th, 5th, last, and second to the last weekday of the month. This field is used for rule_type 2 and 3.

    For example, for time zone string 'Europe Azores', the start date rule is the last Sunday in March; therefore, this field has a value of -1 in the startDST structure to represent the last week of the month.

    loctime

    The local time when DST starts or ends.

    For example, "02:00:00" indicates that DST starts or ends at 2:00 am local time.

    Example  

    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.

    Related Topics

     

    For more information on…

    See…

    Setting session time zones

    SET TIME ZONE, CREATE USER, MODIFY USER in SQL Data Definition Language.

    System time zone settings

    "System TimeZone Hour" and "System TimeZone Minute" in Utilities: Volume 1 (A-K).

    Automatic adjustment of the system time to account for daylight saving time

    "SDF file" and "Teradata Locale Definition Utility (tdlocaledef)" in Utilities: Volume 2 (L-Z).