Rules and Restrictions for Specifying Simple Expressions for Time Zones - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
The following rules and restrictions apply to specifying a simple expression as a time zone value.
  • The simple expression you specify must be a constant; otherwise, the system aborts the request and returns an error to the requestor.
  • The current session time zone can be either a time zone displacement or time zone string.
    IF the current session time zone is … THEN Vantage
    a time zone displacement uses that time zone displacement.
    a time zone string determines the time zone displacement based on the specified time zone string and the UTC TIMESTAMP value for the source expression.

    See Daylight Saving Time and Time Zone Strings Specified As Time Zone Strings for a comprehensive list of valid time zone strings.

  • Vantage implicitly converts the simple expression you specify 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.
    This expression data type … Is implicitly converted as follows …
    • BIGINT
    • BYTEINT
    • INTEGER
    • SMALLINT
    CAST(CAST (expression AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE
    • DECIMAL
    • NUMERIC

    where scale = 0

    CAST(CAST (expression AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE
    • DECIMAL
    • NUMERIC

    where scale > 0

    CAST(CAST((expression)*60 AS INTERVAL MINUTE(4)) AS INTERVAL HOUR(2) TO MINUTE)
    INTERVAL HOUR(2) TO MINUTE no conversion
    INTERVAL HOUR(n) TO MINUTE, where n ¼ 2 CAST(expression) AS INTERVAL HOUR(2) TO MINUTE)
    • INTERVAL HOUR
    • INTERVAL DAY

    If the INTERVAL DAY value you specify is anything other than INTERVAL '0' DAY or INTERVAL -'0' DAY, it eventually fails because it becomes too large to be a time zone displacement.

    • 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)
    CHARACTER with CHARACTER SET UNICODE Attempt to CAST(CAST(AS INTERVAL HOUR(2)) AS INTERVAL HOUR(2) TO MINUTE).

    If an error occurs for the casts, attempt to CAST(expression AS INTERVAL HOUR(2) TO MINUTE).

    If still unable to cast expression, Vantage treats the character value as a time zone string.

    CHARACTER with CHARACTER SET ¼ UNICODE TRANSLATE(expression USING source_repertoire_name_TO_Unicode)

    where source_repertoire_name is the server character set of expression.

    If an error occurs with the translated value, it is then handled as in the previous row for a character value with CHARACTER SET UNICODE replacing expression with the translation of expression.

    anything else The system aborts the request and returns an error to the requestor.
  • Specifying a simple expression without first specifying TIME ZONE is valid syntax and has the same effect as specifying TIME ZONE simple_expression.