16.20 - Rules and Restrictions for Specifying Simple Expressions for Time Zones - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)
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, Teradata Database 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 Teradata Database …
    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.

  • Teradata Database 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(se 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, Teradata Database 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 Teradata Database 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.