Comparison of Period Types | Teradata Vantage - Comparison of Period Types - Advanced SQL Engine - Teradata Database

SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
xmd1556127764262.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1211
lifecycle
previous
Product Category
Teradata Vantageā„¢
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.