Comparison of Period Types | VantageCloud Lake - Comparison of Period Types - Teradata VantageCloud Lake

Lake - Working with SQL
Deployment
VantageCloud
Edition
Lake
Product
Teradata VantageCloud Lake
Release Number
Published
February 2025
ft:locale
en-US
ft:lastEdition
2025-11-21
dita:mapPath
jbe1714339405530.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
jbe1714339405530
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 these rules to allow a CHARACTER and VARCHAR value to be implicitly cast as a Period data type for specific operators and, therefore, have a Period data type. Because the Period data type is the data type of the other Period expression, these Period expressions are 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.

Also, 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. Comparable Period expressions can be compared directly due to this internal representation.

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.