Static, time-related data can be added to tables by adding columns defined to have DateTime data types such as DATE or TIMESTAMP. Such tables are not considered to be temporal tables because the time values are not automatically managed by the database when changes are made to row data.
Temporal tables include one or two derived period columns that represent temporal dimensions. These two dimensions are independent, and used for different purposes.
System time is the time period during which the information in a row is or was known to the database. It reflects the database reality, automatically recording when rows have been added, modified, and deleted in tables that include system time.
The system-time period is represented by a derived period column named SYSTEM_TIME.
You cannot set or modify the values in the component TIMESTAMP columns that are the beginning and ending bound values of the derived system-time period column. Teradata Database maintains these values automatically if the system-time table is created to have system versioning. (This document assumes all system-time tables are created to have system versioning, which is required to enable temporal table behavior.)
Every change to a table that has a system-time dimension is tracked by the database. The physical rows of system-time tables are never deleted, and their substantive data is never modified in these tables:
System-time table rows with end bounds less than the maximum system timestamp value are referred to as “closed,” and can no longer be changed in the database. These rows remain in the table to provide a complete internal history of what happened to the rows in the table. Any prior state of a system-time table can be reproduced. Closed rows are unavailable to most DML modifications, and can only be physically deleted from the table by altering the table definition, whereupon all closed rows are physically deleted from the table permanently.
Use system-versioned system-time tables when historical changes need to be automatically tracked and maintained in the database. For example, system-time tables can be used for some types of regulatory compliance.
For a detailed discussion of system-time tables, see Chapter 3: “Working With ANSI System-Time Tables.”
System Versioning
In order for a system-time table to be considered a temporal table, and to include the automatic timestamping and tracking behaviors characteristic of system-time temporal tables, the table must be designated to have system versioning in the table definition. It is the system versioning that bestows on the table the temporal capabilities. For purposes of discussion in this book assume that, unless otherwise noted, references to “system-time” tables implicitly refer to “system-versioned system-time” tables.
Valid time is the time period during which the information in a row is in effect or true for purposes of real-world application. (ANSI calls this period “application time.”) 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 represented by a derived period column designated by a VALIDTIME column attribute, though the column may have any name.
Consequently, the valid time of a row can span times in the past, present, and future. Rows containing information that is currently valid have valid-time periods that include the current time.
When you add a new row to a table with a valid-time dimension, you must specify the time period during which the row information is valid by including values for the beginning and ending bounds of the valid-time period. Rows containing information that is valid indefinitely are represented practically with an ending bound value of the maximum system date or timestamp value.
When you make a time-bounded change to a row in a valid-time table, the database automatically creates new rows and defines their valid-time periods as necessary to delimit in time when the change was valid, but preserves the original state of the information for periods before and after the change. The nature of these automatic changes are determined by how the time period specified for the change relates to the valid-time period of the rows. For more information on this, see “Modifying Temporal Tables” on page 20.
Use valid-time tables when the information in table rows is delimited by time, and for which row information should be maintained, tracked, and manipulated in a time-aware fashion.
Valid-time tables are 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.
For a detailed discussion of valid-time tables, see Chapter 4: “Working With ANSI Valid-Time Tables.”
System time and valid time are independent time dimensions that are used for different purposes. Bitemporal tables include both dimensions. Changes to bitemporal tables that happen automatically as a result of row modifications are independent for the system-time and valid-time dimensions. These dimensions must be considered separately when determining what will happen to a row in a bitemporal table as a result of a row modification.