15.00 - ANSI DateTime Expressions - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

ANSI DateTime Expressions

Purpose

Perform a computation on a DATE, TIME, or TIMESTAMP value (or value expression) and return a single value of the same type.

Definition

A DateTime expression is any expression that returns a result that is a DATE, TIME, or TIMESTAMP value.

date_time_expression Syntax

date_time_term Syntax

where:

 

Syntax element …

Specifies …

date_time_expression

an expression that evaluates to a DATE, TIME, or TIMESTAMP value.

The form of the expression is one of the following:

  • a single date_time_term.
  • the sum of an interval_expression and a date_time_term expression.
  • the sum or difference of a date_time_expression and an interval_term.
  • date_time_term

    a single date_time_primary or a date_time_primary with a time zone specifier of AT LOCAL, AT [TIME ZONE] expression, or AT [TIME ZONE] time_zone_string.

    interval_expression

    one of the following:

  • a single interval_term.
  • an interval_term added to or subtracted from an interval_expression.
  • the difference between a date_time_expression and a date_time_term (enclosed by parentheses) preceding a start TO end phrase.
  • For more information on interval_expression and interval_term, see “ANSI Interval Expressions” on page 834.

    date_time_primary

    one of the following elements, any of which must have the appropriate DateTime type:

  • Column reference
  • DateTime literal value
  • For details on DateTime literals, see SQL Data Types and Literals.

  • DateTime function reference
  • For example, the result of a CASE expression or CAST function or DateTime built-in function such as CURRENT_DATE or CURRENT_TIME.

  • Scalar function reference
  • Aggregate function reference
  • (table_expression)
  • A scalar subquery.

  • (date_time_timestamp_expression)
  • AT LOCAL

    that the default time zone displacement based on the current session time zone is used. The current session time zone may be specified as a time zone string or a time zone displacement expressed as an Interval data type that defines the local time zone offset.

    AT [TIME ZONE] expression

    that the time zone displacement defined by expression is used. The data type of expression should be INTERVAL HOUR(2) TO MINUTE or it must be a data type that can be implicitly converted to INTERVAL HOUR(2) TO MINUTE.

    AT [TIME ZONE] time_zone_string

    that time_zone_string is used to determine the time zone displacement.

    AT LOCAL and AT TIME ZONE Time Zone Specifiers

    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.

    Note: 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.

    Note: 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. For details, see “GetTimeZoneDisplacement” on page 874.

    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” on page 349.
  • “CURRENT_TIME” on page 355.
  • “CURRENT_TIMESTAMP” on page 359.
  • “DATE” on page 365.
  • “TIME” on page 376.
  • Note: 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. For more information, see Chapter 13: “Data Type Conversions.”
  • With the EXTRACT function to specify a time zone for the source expression before extracting the fields.
  • For more information about time zones, see “DateTime and Interval Data Types” in SQL Data Types and Literals.

    Related Topics

     

    For more information on…

    See…

    Setting session time zones

    SET TIME ZONE, CREATE USER, MODIFY USER in SQL Data Definition Language.

    System time zone settings

    "System TimeZone Hour" and "System TimeZone Minute" in Utilities: Volume 1 (A-K).

    Automatic adjustment of the system time to account for DST

    "SDF file" and "Teradata Locale Definition Utility (tdlocaledef)" in Utilities: Volume 2 (L-Z).

    Gregorian Calendar Rules

    DateTime expressions always operate within the rules of the Gregorian calendar.

    When an evaluation results in a value outside the permissible range for any contained field or results in a value impermissible according to the natural rules for DATE and TIME values, then an error is returned.

    For example, the following operation returns an error because it evaluates to a date that is not valid (‘1996-09-31’).

       SELECT DATE '1996-08-31' + INTERVAL '1' MONTH;

    The desired result is obtained with a slight rephrasing of the second operand.

       SELECT DATE '1996-08-31' + INTERVAL '30' DAY;

    This operation returns the desired result, ‘1996-09-30’. No error is returned.

    Evaluation Types

    Expressions involving DateTime values evaluate to a DateTime type, with DATE being the least significant type and TIMESTAMP the most significant.

     

    DateTime expressions involving …

    Evaluate to a …

    Dates

    date.

    Times

    time.

    Timestamps

    timestamp.

    Adding and Subtracting Interval Values

    DateTime expressions formed by adding an Interval to a DateTime value or by subtracting an Interval from a DateTime value are performed by adding or subtracting values of the appropriate component fields and carrying overflow from lower precision fields with the appropriate modulo to represent proper arithmetic in terms of the calendar and clock.

    An interval_expression or interval_term may only contain DateTime fields that are contained in the corresponding date_time_expression or date_time_term.

    When an Interval value is added to or subtracted from a TIME or TIMESTAMP value, the time zone displacement value associated with the result is identical to that associated with the TIME or TIMESTAMP value.

    Computations With Time Zones

    If you perform arithmetic on DateTime expressions containing time zones, the results are computed in the following way.

    Call the DateTime value of the expression DV and the time zone value component (normalized to UTC) TZ.

    The result is computed as DV - TZ.

    Example : date_time_primary

    In this example, the date_time_primary is a built-in time function.

       SELECT CURRENT_TIME;

    Example : date_time_term With an integer numeric Time Zone Specifier

    In this example, the date_time_primary is a built-in time function and the time zone displacement is specified by the negative integer numeric value for Pacific Standard Time.

    SELECT CURRENT_TIME AT TIME ZONE -8;

    Example : date_time_term With a scaled decimal Time Zone Specifier

    In this example, the date_time_primary is a built-in time function and the time zone displacement is specified by the scaled decimal value for Venezuela's time zone.

    SELECT CURRENT_TIME AT TIME ZONE -4.5;

    Example : date_time_term With an 'hh:mm' string Time Zone Specifier

    In this example, the date_time_primary is a built-in time function and the time zone displacement is specified by the negative 'hh:mm' string for Venezuela's time zone.

    SELECT CURRENT_TIME AT TIME ZONE -'4:30';

    Example : date_time_term With an Interval Column Time Zone Specifier

    In this example, the date_time_term is a date_time_primary column value named f1.

    TS.f1 is a value of type TIME or TIMESTAMP and intrvl.a is a column interval value of type INTERVAL HOUR(2) TO MINUTE.

       SELECT f1 AT TIME ZONE intrvl.a
       FROM TS;

    Example : date_time_term With an Interval Literal Time Zone Specifier

    In this example, the date_time_term is a date_time_primary column value named f1.

    The specified interval is an interval literal value of type INTERVAL HOUR TO MINUTE.

       SELECT f1 AT TIME ZONE INTERVAL '01:00' HOUR TO MINUTE
       FROM TS;

    Example : date_time_term With a Time Zone String Time Zone Specifier

    In this example, the date_time_term is a date_time_primary column value named f1.

    TS.f1 is a value of type TIME or TIMESTAMP and the time zone displacement is based on the time zone string 'America Pacific'.

       SELECT f1 AT TIME ZONE 'America Pacific'
       FROM TS;

    Example : date_time_expression

    In this example, the date_time_expression is an interval_expression added to a date_time_term. Note that you can only add these terms—subtraction of a date_time_term from an interval_expression is not permitted.

       SELECT INTERVAL '20' YEAR + CURRENT_DATE;

    Example : date_time_expression With Addition

    In this example, the date_time_expression is comprised of another date_time_expression added to an interval_term.

    The columns subscribe_date and subscription_interval are typed DATE and INTERVAL MONTH(4), respectively.

       SUBSCRIBE_DATE + SUBSCRIPTION_INTERVAL

    Example : date_time_expression With Subtraction

    You can also subtract an interval_term from a date_time_expression.

    In this example, an interval_term is subtracted from the date_time_expression.

    The columns expiration_date and subscription_interval are typed DATE and INTERVAL MONTH(4), respectively.

       EXPIRATION_DATE - SUBSCRIPTION_INTERVAL

    Time Zone Sort Order

    Time zones are ordered chronologically, using the same time zone.

    Examples  

    Consider the following examples using ordered SELECT statements on a table having a column with type TIMESTAMP(0) WITH TIME ZONE.

    The identical ordering demonstrated in these ORDER BY SELECTs applies to all time zone comparison operations.

       SELECT f1 TIMESTAMPFIELD
       FROM timestwz
       ORDER BY f1;

    This statement returns the following results table.

       TIMESTAMPFIELD
       -------------------------
       1997-10-07 15:43:00+08:00
       1997-10-07 15:43:00-00:00
       1997-10-07 15:47:52-08:00

    Note how the values are displayed with the stored time zone information, but that the ordering is not immediately evident.

    Now note how normalizing the time zones by means of a CAST function indicates chronological ordering explicitly.

       SELECT CAST(f1 AS TIMESTAMP(0)) TIMESTAMP_NORMALIZED
       FROM timestwz
       ORDER BY f1;

    This statement returns the following results table.

       TIMESTAMP_NORMALIZED
       -------------------
       1997-10-06 23:43:00
       1997-10-07 07:43:00
       1997-10-07 15:45:52

    While the ordering is the same as for the previous query, the display of TIMESTAMP values has been normalized to the time zone in effect for the session, which is ‘-08:00’.

    A different treatment of the time zones, this time to reflect local time, indicates the same chronological ordering but from a different perspective.

       SELECT f1 AT LOCAL LOCALIZED
       FROM timestwz
       ORDER BY f1;

    This statement returns the following results table.

       LOCALIZED
       -------------------------
       1997-10-06 23:43:00-08:00
       1997-10-07 07:43:00-08:00
       1997-10-07 15:45:52-08:00