- A transaction-time column records and maintains the time period for which Vantage was aware of the information in the row. Vantage automatically enters and maintains the transaction-time column data, and consequently automatically tracks the history of such information.
- A valid-time column models the real world, and stores information such as the time an insurance policy or product warranty is valid, the length of employment of an employee, or other information that is important to track and manipulate in a time-aware fashion. When you add a new row to this type of table, you use the valid-time column to specify the time period for which the row information is valid. This is the period of validity (PV) of the information in the row.
- A new row is created to represent the changed information that exists following the modification. The beginning bound of its transaction time period value is set to the time of the modification, and its ending bound is left open (set to UNTIL_CLOSED). As far as the database is concerned, the information in the new row is true until it changes again or until the row is deleted.
- The original row represents the row as it existed before the modification. The ending bound of the transaction time period value of the row is set to the time of the modification. This row is “closed” and becomes a history row, because the information it contains from before the modification is no longer true. However, the row is not physically deleted from the database. It remains as a historical record of how the row existed before the modification was made.
- One row has the original information, and a valid-time period that covers the time from the beginning of the original PV of the row until the modification happened.
- The second row has the modified information, and a valid-time period that matches the PA of the modification statement.
- The third row has the original information, like the first row, but has a valid-time period that covers the time starting from after the modification is no longer valid through the end time of the PV of the original row.
If the PA of the modification overlaps, but does not lie within the PV of the row, the modification will split the row into only two rows, similar to the example for a transaction-time table.
Transaction time and valid time are considered independent time dimensions, and their columns serve different purposes, so a table can have both a valid-time column and a transaction-time column. Such a dual-purpose temporal table is called a bitemporal table.