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:
- A copy of the row is automatically created and modified to show the new terms. The PV of the row begins at the time of the change, to show when the new terms started. The PV of the row retains the original ending bound for the valid-time column, to retain the original contract end date.
- The original row, storing the original terms of the contract is marked as a history row. The PV is set to end at the time of the modification, because that is when the old terms ceased to be valid.
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:
- A copy of the row is automatically created and modified to show the new terms. The PV of the row reflects the six weeks for which the new terms are in effect.
- The original row, storing the original terms of the contract, is marked as a history row. The PV is set to end at the time the new terms begin.
- A new row is inserted to reflect the conditions after the six-week change in terms has ended, when the contract reverts to the original terms. The PV for the new row begins when the new terms expire, and ends at the original end time for the original row.
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.