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

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, Teradata Database 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, Teradata Database 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, Teradata Database 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.