How to Track Data with SQL:2011 Bitemporal Tables
Sometimes you want to know both when an event occurred in the real world and when that event was recorded in the SQL database. For cases such as this, you may use a table that is both a system-versioned table and an application-time period table. Such tables are known as bitemporal tables.
There are a number of cases where a bitemporal table might be called for. Suppose, for example, that one of your employees moves her residence across the state line from Oregon to Washington. You must take account of the fact that her state income tax withholding must change as of the official date of the move.
However, it is unlikely that the change to the database will be made on exactly that same day. Both times need to be recorded, and a bitemporal table can do that recording very well. The system-versioned time period records when the change became known to the database, and the application-time period records when the move legally went into effect. Here’s some example code to create such a table:
CREATE TABLE employee_bt ( EmpID INTEGER, EmpStart DATE, EmpEnd DATE, EmpDept Integer PERIOD FOR EmpPeriod (EmpStart, EmpEnd), Sys_Start TIMESTAMP (12) GENERATED ALWAYS AS ROW START, Sys_End TIMESTAMP (12) GENERATED ALWAYS AS ROW END, EmpName VARCHAR (30), EmpStreet VARCHAR (40), EmpCity VARCHAR (30), EmpStateProv VARCHAR (2), EmpPostalCode VARCHAR (10), PERIOD FOR SYSTEM_TIME (Sys_Start, Sys_End), PRIMARY KEY (EmpID, EPeriod WITHOUT OVERLAPS), FOREIGN KEY (EDept, PERIOD EPeriod) REFERENCES Dept (DeptID, PERIOD DPeriod) ) WITH SYSTEM VERSIONING;
Bitemporal tables serve the purposes of both system-versioned tables and application-time tables. The user supplies values for the application-time period start and end columns. An INSERT operation in such a table automatically sets the value of the system-time period to the transaction timestamp. The value of the system-time period end column is automatically set to the highest value permitted for that column’s data type.
UPDATE and DELETE operations work as they do for standard application-time period tables. As is true with system-versioned tables, UPDATE and DELETE operations affect only current rows, and with each such operation a historical row is automatically inserted.
A query made upon a bitemporal table can specify an application-time period, a system-versioned period, or both. Here’s an example of the “both” case:
SELECT EmpID FROM employee_bt FOR SYSTEM TIME AS OF TIMESTAMP '2013-07-15 00:00:00' WHERE EmpID = 314159 AND EmpPeriod CONTAINS DATE '2013-06-20 00:00:00';