17.00 - Valid Time - Teradata Database

Teradata Vantageā„¢ - ANSI Temporal Table Support

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Programming Reference
featnum
B035-1186-170K

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.
  • The beginning bound of the valid-time period is the time when the information in the row commences to be true or in effect.
  • The ending bound of a valid-time period reflects the time after which the information in the row is no longer considered to be true or valid, such as the end date of a contract.

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. If the information in a row is current, but has a finite valid-time period, after sufficient time passes the information ages and becomes no longer valid. At that point the row is considered to be a history row.

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.

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 Working With ANSI Valid-Time Tables.