17.10 - 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.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
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];
    INTEGER          number_of_rules;
    DSTRules         TZRules[TZRulesEntries];
    SMALLINT         Standardtzdispl_hour; 
    SMALLINT         Standardtzdispl_minute; 
} TZwithDST;
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.
The definition of the DSTRules structure is:
typedef struct DSTRules
{
    startendDSTInfo  startDST;
    startendDSTInfo  endDST;
    yearDisplInfo    validyrs;
} DSTRules;
Standardtzdispl_hour
The standard time zone displacement hour.
Standardtzdispl_minute
The standard time zone displacement minute.
startDST
Specifies the date and time when daylight saving time (DST) starts. You can specify the fields in the following table. If a field is not applicable, enter 0.
endDST
Specifies the date and time when daylight saving time ends. You can specify the fields in the following table. If a field is not applicable, enter 0.
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.

startDST and endDST Fields
Field Description
rule_type Indicates how the start and end date for DST is specified. The valid values are:
Value Meaning
0 Unspecified. Defaults to standard time zone displacement.
1 DST starts or ends on specified fixed date, which is specified by month and day_of_month fields.
2 DST starts or ends on the 1st, 2nd, or 3rd weekday of month as indicated by 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 month, day_of_week, and week_of_month fields.
4 DST starts or ends on next weekday or immediately after date specified in day_of_month field. Month and weekday are specified in month and day_of_week fields.

For example, for time zone string 'America Pacific', start date rule is first Sunday after March 8 (March 14 in 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.