UNTIL_CHANGED and UNTIL_CLOSED | Temporal Table Support | Teradata Vantage - 17.05 - UNTIL_CHANGED and UNTIL_CLOSED - Teradata Database

Teradata Vantage™ - Temporal Table Support

Advanced SQL Engine
Teradata Database
June 2020
Programming Reference
UNTIL_CHANGED and UNTIL_CLOSED are special values that represent the ending bound of periods for which the duration is indefinite, forever, or for which the end is an unspecified and unknown time in the future when the row will be changed. They are used in some circumstances when a new row is inserted into a temporal table:
  • All new rows that have transaction-time columns are assigned transaction-time periods with end bounds of UNTIL_CLOSED. UNTIL_CLOSED is only associated with transaction-time columns.
  • New rows that have valid-time columns can be assigned valid-time periods with end bounds of UNTIL_CHANGED to represent that the information in the row is valid indefinitely.

UNTIL_CLOSED has a data type of TIMESTAMP(6) WITH TIME ZONE and a value of TIMESTAMP '9999-12-31 23:59:59.999999+00:00'.

The value of UNTIL_CHANGED depends on the data type and precision of the valid-time column. If the type is PERIOD(DATE), UNTIL_CHANGED is the value DATE '9999-12-31'. If the type is PERIOD(TIMESTAMP), UNTIL_CHANGED is the value of TIMESTAMP '9999-12-31 23:59:59.999999+00:00', with precision and time zone matching that specified for the valid-time data type.

For more information on UNTIL_CHANGED and UNTIL_CLOSED, see the discussion of Period data types in Teradata Vantage™ - Data Types and Literals, B035-1143.