Transaction-Time Timestamping - Advanced SQL Engine - Teradata Database

Temporal Table Support

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
cjo1556732840654.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1182
lifecycle
previous
Product Category
Teradata Vantage™

When a row is added to, or modified in a temporal table with transaction time, the system automatically timestamps the transaction-time column to indicate when the system became aware of the new or modified information in the row.

By default, the timestamp used for transaction-time columns is the value read from the system clock by each AMP at the instant the row is inserted or modified. This value is referred to as TT_TIMESTAMP throughout this documentation:

  • The beginning bound of the transaction-time period is automatically set to TT_TIMESTAMP for rows inserted into tables with transaction time.
  • The ending bound of the transaction-time period is automatically set to TT_TIMESTAMP for rows that are modified in tables with transaction time. This maintains a history of when the change to the row occurred.

This automatic timestamping process produces different timestamps for each row within the same load job, and for each row within the same transaction. That means that all modifications, even those within a single transaction, are individually tracked by the database for tables that have a transaction-time column. For example, a transaction consisting of two statements, where one statement inserts a row and the other statement deletes the previously inserted row leaves a track in the database in the form of a stored history row that is closed in transaction time, and unavailable to most SQL.

If a single modification to a row results in multiple rows being automatically added to the database, the system uses the same TT_TIMESTAMP value to timestamp all affected rows. For example, an update to a row of a table with a transaction-time column could result in an update to the original row, plus the insertion of one or two new rows. In this case, TT_TIMESTAMP would be the same time for all rows. For examples of how a modification to one row can result in one or two additional rows being added to the temporal table, see Sequenced Updates.

The granularity of transaction-time timestamping can be changed by means of the SET SESSION TTGRANULARITY statement. For more information, see SET SESSION TTGRANULARITY TO.