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 Vantage was aware of the information in the row. Vantage 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, Vantage 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 table.