Transaction-Time Timestamping - Analytics Database - Teradata Vantage

Temporal Table Support

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
ft:locale
en-US
ft:lastEdition
2023-10-30
dita:mapPath
eud1628112402879.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
fif1472244754791
lifecycle
latest
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.