Modifying Rows in ANSI Bitemporal Tables - Analytics Database - Teradata Vantage

ANSI Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
jqu1628112571823.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esa1472244798285
lifecycle
latest
Product Category
Teradata Vantageā„¢

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.