17.00 - 17.05 - The TZ_DST Structure - 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)

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.