System time is the time period during which the information in a row is or was known to the database. It reflects the database reality, automatically recording when rows have been added, modified, and deleted in tables that include system time.
- The beginning bound of the system-time period is the time when the database became aware of a row. It records when every row was added to the table.
- The ending bound of a system-time period reflects when any of the information in an existing row was modified, or when the row was deleted from the table. Rows containing information that is currently in effect have system-time periods with indefinite ending bounds, represented practically as the maximum system timestamp value (9999-12-31 23:59:59.999999+00:00).
You cannot set or modify the values in the component TIMESTAMP columns that are the beginning and ending bound values of the derived system-time period column. Vantage maintains these values automatically if the system-time table is created to have system versioning. (This document assumes all system-time tables are created to have system versioning, which is required to enable temporal table behavior.)
- When a row is explicitly deleted from a system-time table, the row remains in the table, but the value of the system-time end bound is timestamped to reflect the time of the deletion.
- When a row is modified in a system-time table, the original row with the original values is logically deleted, but the physical row remains in the table. The value of end bound of the system-time period for the original row is timestamped to reflect the time of the modification. A copy of the row having the new, modified values is automatically inserted into the table, and its system-time starting bound is timestamped with the time of the modification.
System-time table rows with end bounds less than the maximum system timestamp value are referred to as “closed,” and can no longer be changed in the database. These rows remain in the table to provide a complete internal history of what happened to the rows in the table. Any prior state of a system-time table can be reproduced. Closed rows are unavailable to most DML modifications, and can only be physically deleted from the table by altering the table definition, whereupon all closed rows are physically deleted from the table permanently.
Use system-versioned system-time tables when historical changes need to be automatically tracked and maintained in the database. For example, system-time tables can be used for some types of regulatory compliance.
For a detailed discussion of system-time tables, see Working With ANSI System-Time Tables.
System Versioning
In order for a system-time table to be considered a temporal table, and to include the automatic timestamping and tracking behaviors characteristic of system-time temporal tables, the table must be designated to have system versioning in the table definition. It is the system versioning that bestows on the table the temporal capabilities. For purposes of discussion in this documentation, assume that references to "system-time" tables implicitly refer to "system-versioned system-time" tables unless otherwise noted.