15.00 - Temporal Row Types - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

Temporal Row Types

A row in a valid-time table can be a current row, future row, or history row.

A row in a transaction-time table can be an open or closed row.

A row in a bitemporal table can be a current row, future row, or history row in the valid-time dimension and an open row or closed row in the transaction-time dimension.

Current Row

In a valid-time table, a current row is a row with a valid-time period that overlaps the current time. A row with a valid-time value of NULL is not considered a current row.

In a bitemporal table, a current row has a valid-time period that overlaps the current time, and a transaction-time period that is open.

Future Row

In a valid-time table, a future row is a row with a valid-time period that begins after the current time. A row with a valid-time value of NULL is not considered a future row.

Transaction-time tables never have future rows. The transaction-time period of a row in a transaction-time table begins at the time the row is created in the transaction-time table.

In a bitemporal table, a future row is a row with a valid-time period that begins after the current time, and a transaction-time period that is open.

History Row

In a valid-time table, a history row is a row that is no longer valid: a row with a valid-time period that ends before the current time. A row with a valid-time value of NULL is not considered a history row.

In a transaction-time table, a row that is closed is also considered to be a history row, regardless of the valid-time period. See “Closed Row” on page 23.

In a bitemporal table, a history row is a row with a valid-time period that ends before the current time, or a row that is closed in transaction time, or a row that has both of these conditions. A row with a valid-time value of NULL is not considered a history row.

Open Row

An open row is a row that is currently known to the database, a row that is currently in effect in the database. It is a row that has not been (logically) deleted from the database or superseded by a row modification.

In a transaction-time table, an open row is a row with a transaction-time period that has an ending bound of UNTIL_CLOSED. When a new row is added to a table with transaction time, the ending bound of the transaction-time column period value is set to UNTIL_CLOSED, and the original row is considered to be open until the row is modified or deleted.

UNTIL_CLOSED has a value of TIMESTAMP '9999-12-31 23:59:59.999999+00:00'.

In a valid-time table, all rows are considered to be open. When a row is deleted from a valid-time table, the row is physically deleted from the database, rather than closed. See “Valid Row” on page 23.

In a bitemporal table, an open row is a row with a transaction-time period that has an ending bound of UNTIL_CLOSED. Open rows that are no longer valid in the valid-time dimension are considered history rows, even though they remain open in the transaction-time dimension. See “History Row” on page 22.

Closed Row

A closed row is a row that is no longer in effect in the database. It is a row that has either been (logically) deleted from the database, or otherwise superseded by a row modification, which closes the original row, and opens a new row with the changed information.

In a transaction-time or bitemporal table, a closed row is a row with a transaction-time period that has an ending bound different from UNTIL_CLOSED (9999-12-31 23:59:59.999999+00:00). Such a row is said to be closed in transaction time.

The concept of a closed row does not apply to valid-time tables. See “No Longer Valid Row” on page 24.

Valid Row

A valid row is a current or future row. It is a row in a valid-time or bitemporal table with a valid-time period that overlaps current time, or that begins in the future.

For bitemporal tables, only rows that are open in the transaction-time dimension can be valid.

The concept of a valid row does not apply to a transaction-time table.

No Longer Valid Row

A row that is no longer valid is a row in a valid-time or bitemporal table with a valid-time period that ends before the current time. It is considered to be a history row in the valid-time dimension.

For bitemporal tables, only rows that are open in the transaction-time dimension can be considered no longer valid. Rows that are closed in the transaction-time dimension of a bitemporal table are considered history rows, regardless of their valid-time period, and are not considered either valid or no longer valid.