Comparison of Period Types | Teradata Vantage - 17.00 - 17.05 - Comparison of Period Types - 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)
Data Type Primary fields
DATE YEAR, MONTH, and DAY
TIME HOUR, MINUTE, and SECOND
TIMESTAMP YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND
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 zeros. 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.

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.