Overview: Temporal Tables
Temporal tables store and maintain information with respect to time. Using temporal
tables, Teradata Database can process statements and queries that include time-based
reasoning. Temporal tables include one or two special columns, which store time information:
A transaction-time column records and maintains the time period for which Teradata
Database was aware of the information in the row. Teradata Database automatically
enters and maintains the transaction-time column data, and consequently automatically
tracks the history of such information.
A valid-time column models the real world, and stores information such as the time
an insurance policy or product warranty is valid, the length of employment of an employee,
or other information that is important to track and manipulate in a time-aware fashion.
When you add a new row to this type of table, you use the valid-time column to specify
the time period for which the row information is valid. This is the period of validity
(PV) of the information in the row.
As rows are changed in temporal tables, the database automatically creates new rows
as necessary to maintain the time dimensions. For example, if a row in a table with
transaction time is modified, the row is automatically split into two rows:
A new row is created to represent the changed information that exists following the
modification. The beginning bound of its transaction time period value is set to the
time of the modification, and its ending bound is left open (set to UNTIL_CLOSED).
As far as the database is concerned, the information in the new row is true until
it changes again or until the row is deleted.
The original row represents the row as it existed before the modification. The ending
bound of the transaction time period value of the row is set to the time of the modification.
This row is “closed” and becomes a history row, because the information it contains
from before the modification is no longer true. However, the row is not physically
deleted from the database. It remains as a historical record of how the row existed
before the modification was made.
Modifications to rows in table with a valid-time column are more flexible. When a
row is modified in a table with a valid-time column, you can specify the time period
for which the modification applies. This is the period of applicability (PA) of the
modification. Depending on the relationship between the PV of the row and the PA of
the modification, Teradata Database may split the modified row into multiple rows.
For example, if the modification is applicable only to a brief period that lies within
the PV of the row, three rows will result from a modification:
One row has the original information, and a valid-time period that covers the time
from the beginning of the original PV of the row until the modification happened.
The second row has the modified information, and a valid-time period that matches
the PA of the modification statement.
The third row has the original information, like the first row, but has a valid-time
period that covers the time starting from after the modification is no longer valid
through the end time of the PV of the original row.
If the PA of the modification overlaps, but does not lie within the PV of the row,
the modification will split the row into only two rows, similar to the example for
a transaction-time table.
Transaction time and valid time are considered independent time dimensions, and their
columns serve different purposes, so a table can have both a valid-time column and
a transaction-time column. Such a dual-purpose temporal table is called a bitemporal