15.00 - Comparison of Period Types - 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)

Comparison of Period Types

Two Period values are comparable if their element types are of same DateTime data type. The DateTime data types are DATE, TIME and TIMESTAMP.

  • The PERIOD(DATE) date type is comparable with the PERIOD(DATE) data type.
  • The PERIOD(TIME(n)[WITH TIME ZONE]) data type is comparable with a PERIOD(TIME(m)[WITH TIME ZONE]) data type,
  • The PERIOD(TIMESTAMP(n)[WITH TIME ZONE]) data type is comparable with a PERIOD(TIMESTAMP(m)[WITH TIME ZONE]) data type.
  • Teradata extends this to allow a CHARACTER and VARCHAR value to be implicitly cast as a Period data type for some operators and, therefore, have a Period data type. Since the Period data type is the data type of the other Period expression, these Period expressions will be comparable.

    DateTime and Period data are saved internally with the maximum precision of 6, although the specified precision may be less than this and is padded with zeroes. Thus, the comparison operations with differing precisions work without any additional logic.

    In addition, the internal value is saved in UTC for a Time or Timestamp value, or for a Period value with an element type of TIME or TIMESTAMP. All comparable Period expressions can be compared directly due to this internal representation irrespective of whether they contain a time zone value, or whether they have the same precision.

    Note: The time zone values are ignored when comparing values.

    All comparison operations involving UNTIL_CLOSED in a system-versioned system-time or temporal table transaction-time column use the internal value of UNTIL_CLOSED (TIMESTAMP '9999-12-31 23:59:59:999999+00:00') to evaluate the result. For more information about temporal tables, see ANSI Temporal Table Support and Temporal Table Support.

    Comparison Operators

    The following table describes the comparison operators.

     

    Operator

    Purpose

    EQ or =

    Assume p1 and p2 are Period expressions and have comparable Period data types.

    If BEGIN(p1) = BEGIN(p2) AND END(p1) = END(p2), the result of the comparison is TRUE; otherwise, the result is FALSE.

    If either Period expression is NULL, the result is UNKNOWN.

    If the Period expressions have different element types, one of them must be explicitly CAST as the other.

    If one Period expression has a Period data type and the other Period expression has CHARACTER or VARCHAR data type, the CHARACTER or VARCHAR expression is implicitly converted, before comparison, to the data type of the Period expression based on the format of the Period expression.

    EQ supports comparisons between derived periods.

    LT or <

    Assume p1 and p2 are Period expressions and have comparable Period data types.

    If BEGIN(p1) < BEGIN(p2) OR (BEGIN(p1) = BEGIN(p2) AND END(p1) < END(p2)), the result of the comparison is TRUE; otherwise, the result is FALSE.

    If either Period expression is NULL, the result is UNKNOWN.

    If the Period expressions have different element types, one of them must be explicitly CAST as the other.

    If one Period expression has a Period data type and the other Period expression has CHARACTER or VARCHAR data type, the CHARACTER or VARCHAR operand is implicitly converted, before comparison, to the data type of the Period expression based on the format of the Period expression.

    If the ending bound value of a system-versioned system-time or temporal table transaction-time column is UNTIL_CLOSED, the ending bound value is only less than a TIMESTAMP column value or TIMESTAMP literal if the column value or literal is the maximum TIMESTAMP value with leap seconds. This can be possible only if the ending bound of the system-versioned system-time or transaction-time column is used in a comparison with the timestamp value. For more information about temporal tables, see ANSI Temporal Table Support or Temporal Table Support.

    LT supports comparisons between derived periods.

    GT or >

    Assume p1 and p2 are Period expressions and have comparable Period data types.

    If BEGIN(p1) > BEGIN(p2) OR (BEGIN(p1) = BEGIN(p2) AND END(p1) > END(p2)), the result of the comparison is TRUE; otherwise, it is FALSE.

    If either Period expression is NULL, the result is UNKNOWN.

    If one Period expression has a Period data type and the other Period expression has CHARACTER or VARCHAR data type, the CHARACTER or VARCHAR Period expression is implicitly converted, before comparison, to the data type of the Period expression based on the format of the Period expression.

    GT supports comparisons between derived periods.

    NE or <> or
    NOT= or ^= or
    LE or <= or
    GE or >=

    These comparison operators are supported for comparable Period expressions.

    If one Period expression has a Period data type and the other Period expression has CHARACTER or VARCHAR data type, the CHARACTER or VARCHAR Period expression is implicitly converted, before comparison, to the data type of the Period expression based on the format of the Period expression.

    NE, LE, and GE support comparisons between derived periods.