Transaction Time and Valid Time
Static, time-related columns can be added to tables by adding columns defined to have DateTime data types, such as DATE or TIMESTAMP. Teradata also supports two built-in time dimensions that can be used to create temporal tables: transaction time and valid time.
Each of these time dimensions is represented by a column with a period data type. The column stores a pair of DATE or TIMESTAMP values that define the beginning and end of the transaction- or valid-time period for a row. Transaction time and valid time are independent time dimensions. A table can have either type of column, both, or neither:
Transaction time is the time period during which a fact, represented by all the information in a row, is or was known to be in effect in the database. It models the database reality, recording when rows have been added, modified, and changed in the database. Transaction-time periods are stored in a transaction-time column:
Transaction-time columns are defined by specifying AS TRANSACTIONTIME in the column definition, and have a period data type with an element type of TIMESTAMP(6) WITH TIME ZONE. You cannot normally set or modify the value of a transaction-time column. Teradata Database maintains these values automatically. (However, for database maintenance and troubleshooting, closed rows can be modified or deleted by administrators who have been granted the NONTEMPORAL privilege.)
Every change to a table that has a transaction-time column is tracked by the database. In a sense, physical rows are never deleted or modified in tables that have a transaction-time column:
The resulting snapshots of deleted and modified rows, which are retained in the table, provide a complete internal history of the table. Any prior state of a table having a transaction-time column can be reproduced. However, closed rows are unavailable to most DML modifications or deletions.
Add transaction-time columns to tables for which historical changes should be automatically tracked and maintained in the database. For example, transaction-time tables can be used for information that must retain a history of all changes, such as for tables used for regulatory compliance reporting.
Valid time models the real world, and denotes the time period during which a fact, represented by all the information in a row, is in effect or true. Valid-time periods are stored in a valid-time column. Valid-time columns store information such as the time an insurance policy or contract is valid, the length of employment of an employee, or other information that is important to track and manipulate in a time-aware fashion. The valid-time period is also known as the period of validity (PV) of the row.
Valid-time columns are defined by specifying AS VALIDTIME in the column definition, and have a period data type with an element type of DATE or TIMESTAMP(n) (optionally including WITH TIME ZONE). You specify the value of the valid-time column when a new row is inserted into the table.
Teradata Database automatically maintains the valid-time column for rows that are changed or deleted, according to how the time period specified for the change or deletion relates to the original PV of the row.
For example, assume a row in a valid-time table represents the terms of a contract that is valid for two years. If the terms (row) must be modified during the contract period:
Such a modification changes the row information starting at the current time of the modification, and the change is valid throughout the remaining PV of the row.
Modifications to tables that have valid-time columns can also apply to specified time periods, even periods that do not overlap the current time, such as times that have passed or that are in the future. The changes will affect only those rows with PVs that overlap the specified time period, and only for the period during which the change is applicable. Other kinds of modifications to these tables can affect rows for their entire PVs, much like changes to nontemporal tables.
For example, if the terms of the contract in the example above were changed for only six weeks during the middle of the two-year contract period, the change would automatically yield three rows in the table:
In this way, valid-time tables also keep an automatic history of all changes. Unlike transaction-time, however, history rows in tables with valid-time remain accessible to temporal SQL queries and DML. Because they model the real world, valid-time tables can have rows with a PV in the future, because things like contracts and policies may not begin or end until a future date.
Add valid-time columns to tables for which the information in a row is delimited by time, and for which row information should be maintained, tracked, and manipulated in a time-aware fashion. A valid time column is most appropriate when changes to rows occur relatively infrequently. To represent attributes that change very frequently, such as a point of sale table, an event table is preferable to a valid-time table. Temporal semantics do not apply to event tables.
Transaction time and valid time are independent time dimensions that are used for different purposes. Bitemporal tables have both a transaction-time column and a valid-time column. Changes to bitemporal tables that happen automatically as a result of row modifications are independent for the transaction-time and valid-time dimensions. These dimensions must be considered separately when determining what will happen to a row as a result of a modification.
For example, if a row in a bitemporal table is deleted, the ending bound of the transaction-time period is automatically changed to reflect the time of the deletion, and the row is closed to further modifications. The database reality, reflected by the modified ending bound of the transaction-time period, is that the row has been deleted.
The valid-time period of the row remains unchanged. Because the deletion does not affect the ending bound of the valid-time period, the row information retains its character in the valid-time dimension as historical, current, or future information. However, because the row was deleted, the row does not participate in further DML operations for the table, even though it remains in the table as a closed row in transaction time.
Because of the transaction-time column, all modifications to rows in bitemporal tables automatically create closed rows in the transaction time dimension, just as they do for transaction-time tables. This is in addition to rows that might be created to account for changes in the valid-time dimension.
For example, assume the terms of a contract are stored in a row of a bitemporal table. If the terms are changed during the period when the contract is valid, the row must be modified, as with an UPDATE statement. Because this is a temporal table, Teradata Database automatically inserts a copy of the row to store the new terms. The period of validity of the new row is automatically set to begin at the time of the change, and end at the original end date of the contract. The beginning bound of the transaction-time period of the new row reflects when the new row was created.
The original row is automatically modified to have the end of the period of validity reflect the time of the change, when the old terms become no longer valid. This row becomes a history row in the valid-time dimension. Note that both rows remain open rows in the transaction time dimension, and as such, are still available to all types of DML queries and modifications. These changes are purely a result of the valid-time dimension of the table.
Because the table also includes a transaction-time dimension, another copy is made of the original row, reflecting the original period of validity, but the row is closed in the transaction time dimension at the time the terms changed. No further changes can be made to this row, because it is closed in transaction time. It provides a permanent “before” snapshot of the row as it existed in the database before it was changed.
Note that the actions which are performed automatically by Teradata Database on the row include independent actions that result from the table having both a valid-time column and a transaction-time column.
For more information on...
UNTIL_CHANGED and UNTIL_CLOSED
creating temporal tables
history, current, future, open, and closed rows
NONTEMPORAL temporal qualifier
CREATE_TABLE (temporal form)