17.00 - 17.05 - AT LOCAL and AT TIME ZONE Time Zone Specifiers - 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)

A date_time_primary can include an AT LOCAL or AT [TIME ZONE] clause only if the date_time_primary evaluates to a TIME or TIMESTAMP value or is the built-in function CURRENT_DATE or DATE.

The effect is to adjust date_time_term to be in accordance with the specified time zone displacement.

The expression that specifies the time zone displacement in an AT [TIME ZONE] clause is implicitly converted, as needed and if allowed, to a time zone displacement or time zone string depending on its data type as defined in the following table.

Data type of expression Implicit Conversion
INTERVAL HOUR(n) TO MINUTE

   where n is not 2

CAST(expression AS INTERVAL HOUR(2) TO MINUTE)
  • INTERVAL HOUR
  • INTERVAL DAY
  • INTERVAL DAY TO HOUR
  • INTERVAL DAY TO MINUTE
  • INTERVAL DAY TO SECOND
  • INTERVAL HOUR
  • INTERVAL HOUR TO SECOND
  • INTERVAL MINUTE
  • INTERVAL MINUTE TO SECOND
  • INTERVAL SECOND
CAST(expression AS INTERVAL HOUR(2) TO MINUTE)
  • BYTEINT
  • SMALLINT
  • INTEGER
  • BIGINT
  • DECIMAL/NUMERIC if the fractional precision is 0
CAST(CAST(expression AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE)
DECIMAL/NUMERIC if the fractional precision is greater than 0 CAST(CAST((expression)*60 AS INTERVAL MINUTE(4)) AS INTERVAL HOUR(2) TO MINUTE)
Character with CHARACTER SET UNICODE CAST(CAST(expression AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE)

If an error occurs for the above CAST statement, Teradata Database attempts the following:

CAST(expression AS INTERVAL HOUR(2) TO MINUTE)

If an error occurs for this CAST statement also, Teradata Database treats the character value as a time zone string.

Character that is not CHARACTER SET UNICODE TRANSLATE(expression USING source_repertoire_name_TO_Unicode)

where source_repertoire_name is the server character set of expression. The translated value is then processed as above for a character value with CHARACTER SET UNICODE.

other An error is returned.
There is a general restriction that in Numeric-to-Interval conversions, the INTERVAL type must have only one DateTime field. However, this restriction is not an issue when implicitly converting the expression of an AT clause because the conversion is done with two CAST statements.

If the conversion to INTERVAL HOUR(2) TO MINUTE results in a value that is not between INTERVAL -'12:59' HOUR TO MINUTE and INTERVAL '14:00' HOUR TO MINUTE, an error is returned.

You can specify two kinds of time zone strings in the AT [TIME ZONE] time_zone_string clause:
  • Time zone strings that do not follow separate daylight savings time (DST) and standard time zone displacements from Coordinated Universal Time (UTC) time.
  • Time zone strings that follow different DST and standard time zone displacements from UTC time.

The following time zone strings are supported.

Strings that do not follow separate DST and standard time zone displacements
  • 'GMT'
  • 'GMT+1'
  • 'GMT+10'
  • 'GMT+11'
  • 'GMT+11:30'
  • 'GMT+12'
  • 'GMT+13'
  • 'GMT+14'
  • 'GMT+2'
  • 'GMT+3'
  • 'GMT+3:30'
  • 'GMT+4'
  • 'GMT+4:30'
  • 'GMT+5'
  • 'GMT+5:30'
  • 'GMT+5:45'
  • 'GMT+6'
  • 'GMT+6:30'
  • 'GMT+7'
  • 'GMT+8'
  • 'GMT+8:45'
  • 'GMT+9'
  • 'GMT+9:30'
  • 'GMT-1'
  • 'GMT-10'
  • 'GMT-11'
  • 'GMT-2'
  • 'GMT-3'
  • 'GMT-4'
  • 'GMT-5'
  • 'GMT-6'
  • 'GMT-6:30'
  • 'GMT-7'
  • 'GMT-8'
Strings that follow different DST and standard time zone displacements
  • 'Africa Egypt'
  • 'Africa Morocco'
  • 'Africa Namibia'
  • 'America Alaska'
  • 'America Aleutian'
  • 'America Argentina'
  • 'America Atlantic'
  • 'America Brazil'
  • 'America Central'
  • 'America Chile'
  • 'America Cuba'
  • 'America Eastern'
  • 'America Mountain'
  • 'America Newfoundland'
  • 'America Pacific'
  • 'America Paraguay'
  • 'America Uruguay'
  • 'Asia Gaza'
  • 'Asia Iran'
  • 'Asia Iraq'
  • 'Asia Irkutsk'
  • 'Asia Israel'
  • 'Asia Jordan'
  • 'Asia Kamchatka'
  • 'Asia Krasnoyarsk'
  • 'Asia Lebanon'
  • 'Asia Magadan'
  • 'Asia Omsk'
  • 'Asia Syria'
  • 'Asia Vladivostok'
  • 'Asia West Bank'
  • 'Asia Yakutsk'
  • 'Asia Yekaterinburg'
  • 'Australia Central'
  • 'Australia Eastern'
  • 'Australia Western'
  • 'Europe Central'
  • 'Europe Eastern'
  • 'Europe Kaliningrad'
  • 'Europe Moscow'
  • 'Europe Samara'
  • 'Europe Western'
  • 'Indian Mauritius'
  • 'Mexico Central'
  • 'Mexico Northwest'
  • 'Mexico Pacific'
  • 'Pacific New Zealand'
  • 'Pacific Samoa'

Teradata Database resolves the time zone string and calculates the time zone displacement for the session or requested query.

Teradata Database will automatically adjust the time zone displacement to account for the start or end of daylight saving time only if you specify a time zone using a time zone string that follows different DST and standard time zone displacements. GMT format strings represent time zone strings that follow only one standard time and does not have a separate daylight saving time. For example, the time zone string 'GMT+5:30' can be used for India in order to use the displacement interval 5:30, which is applicable all year around.

Teradata Database resolves the time zone string based on the rules and time zone displacement information stored in the system UDF (user-defined function), GetTimeZoneDisplacement.

If the time zone strings provided by Teradata do not meet your requirements, you may add new time zone strings or modify the existing time zone strings by modifying or adding new rules to the GetTimeZoneDisplacement UDF.

You can also use the AT clause to explicitly specify a time zone in the following cases:
  • With the following built-in functions:
    • “CURRENT_DATE”.
    • “CURRENT_TIME”.
    • “CURRENT_TIMESTAMP”.
    • “DATE”.
    • “TIME”.
      If you specify these built-in functions with an AT LOCAL clause, the value returned depends on the setting of the DBS Control flag TimeDateWZControl.
  • When converting DateTime data types using the CAST function or Teradata conversion syntax. You can specify the time zone used for the CAST or conversion as the source time zone, a specific time zone displacement or time zone string, or the current session time zone.
  • With the EXTRACT function to specify a time zone for the source expression before extracting the fields.