17.10 - Modifying Rows in ANSI Bitemporal Tables - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1186-171K
Language
English (United States)

Because system time and valid time are independent dimensions any change to a bitemporal table affects each dimension independently. The behavior of bitemporal tables can be best understood by considering the consequences of changes to each dimension alone.

In the system-time dimension:
  • Any change to an existing row marks the original row as a closed row, with the end of the system-time period timestamped to reflect the time of the change.
    • If the change was a deletion, no new rows are added to the table due to the system time.
    • If the change updated the information in the row, Vantage automatically inserts a new row into the table to reflect the new information, and the original row is marked as closed, with system time ending at the time of the change.
      Because the system-time and valid-time dimensions are independent, valid-time temporal changes to a row are treated the same as non-temporal changes, with respect to system time. Such changes cause the original, unchanged row to be closed in system time and stored in the table as a record of how the table existed before the change.
  • If a row is inserted into the table, Vantage timestamps the beginning bound of the system-time period with the time of the insertion, and the ending bound is marked as 9999-12-31 12:59:59:999999+00:00.
  • In a bitemporal table as in system-time tables, closed rows do not participate in most SQL operations. because these rows are considered to have been deleted from the table.
For the valid-time dimension, the results of changes depends on the relationship between the PA of the change and the PV of the row:
  • If the PA overlaps a portion of the PV, Vantage automatically inserts a new row into the table to reflect the new information. Depending on how the PA and PV overlap, the PV of the new row will start or end at the same valid time as the original row, and the other valid-time bound will be automatically timestamped with the date or timestamp of the modification.
  • If the PA lies within the PV of the row, two new rows will be generated so that there are rows in the table to reflect the changed row in addition to the original state of the row both before and after the change.
  • If the PA completely overlays the PV of the row, the change is similar to a nontemporal modification.
Only rows that are still open in system time are subject to DELETE and UPDATE modifications in bitemporal tables.