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.