15.00 - Transaction Time and Valid Time - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

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:

  • A table with a transaction-time column is called a transaction-time table.
  • A table with a valid-time column is called a valid-time table.
  • A table with both a transaction-time and a valid-time column is called a bitemporal table.
  • A table with neither a transaction-time nor a valid-time column is a nontemporal table.
  • Transaction Time

    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:

  • The beginning of the transaction-time period is the time when the database became aware of a row, when the row was first recorded in the database. This is when the row was added to a table.
  • The end of a transaction time period reflects when the fact was superseded by an update to the row, or when the row was deleted from the database. Rows containing information that is currently in effect have transaction-time periods with indefinite ending bounds, represented as UNTIL_CLOSED.
  • 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:

  • When a row is “deleted” from the table, the row is not physically deleted from the table. Instead, the transaction-time column is automatically modified to have an ending bound that specifies the time of the deletion, which marks the row as “closed,” and no longer available.
  • When a row is “modified” in the table, the original row with the original values is marked as closed, and a copy of the row having the modified values is automatically inserted into the table.
  • 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

    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.

    Bitemporal 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.

    Related Information

     

    For more information on...

    See...

    temporal timestamping

    “Timestamping” on page 27

    UNTIL_CHANGED and UNTIL_CLOSED

    “UNTIL_CHANGED and UNTIL_CLOSED” on page 21

    creating temporal tables

    Chapter 3: “Creating Temporal Tables”

    history, current, future, open, and closed rows

    “Temporal Row Types” on page 22

    NONTEMPORAL temporal qualifier

    “Nontemporal Operations” on page 25

    CREATE_TABLE (temporal form)

    “CREATE TABLE (Temporal Form) CREATE TABLE ... AS” on page 65