How to Run a Script with phpMyAdmin in SQL for HTML5and CSS3 Programming
How to Use SQL with Microsoft Access
How to Implement a Database in MySQL for HTML5 and CSS3 Programming

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';
  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
How to Calculate Date Values in SQL Data for HTML5and CSS3 Programming
More SQL Server 2005 Express Troubleshooting Tips
6 Sources of Information on SQL Server 2008
PHP MySQL Functions
Changes for the ALTER Query in PHP and MySQL Web Development
Advertisement

Inside Dummies.com