Temporal Tables Part 5: Bitemporal Tables and Queries
This is part 5 of a 5-part series, if you want to start from the beginning see Temporal Tables Part 1: Introduction & Use Case Example
In this blog series we have covered so far, the following temporal tables topics:
Part 1: Introduction and use case example
Part 2: Use the system time versioned tables
Part 3: Manage historical data growth
Now let’s put it all together by combining some of these approaches.
Bitemporal tables manages both system time and application time. This combination enables applications to manage business validity of their data while MariaDB keeps a full history of any updates and deletes. This opens new use cases, like retrospective and trend data analysis, forensic discovery, or data auditing.
When creating a bitemporal table, you combine the steps used to create a system time temporal table with the steps used to create an application time temporal table. In this example we start by creating an employee’s table with application time enabled:
CREATE TABLE Employees ( empID INTEGER , firstName VARCHAR(100) , lastName VARCHAR(100) , address VARCHAR(100) , city VARCHAR(100) , state VARCHAR(50) , zip VARCHAR(20) , departmentName VARCHAR(20) , startDate DATETIME NOT NULL , endDate DATETIME NOT NULL , PERIOD FOR appl_time (startDate, endDate) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
And populate it with some sample rows:
Using the following statement, we can update Georgi’s record to include the period of time he worked in the marketing department:
UPDATE Employees FOR PORTION OF appl_time FROM '2016-01-01' to '2016-06-30' SET departmentName = 'Marketing' WHERE empId = 2;
Like before a simple SELECT statement is all you need to list the departments Georgi has worked for and on which dates:
SELECT firstName , lastName , address , departmentName , startDate , endDate FROM Employees WHERE empID = 2 ORDER BY startDate;
Now we need to add versioning to establish the link between the application time and system time for the employee’s table.
ALTER TABLE Employees ADD SYSTEM VERSIONING;
The ALTER TABLE above will add the WITH SYSTEM VERSIONING clause to the table: CREATE TABLE Employees ( empID INTEGER , firstName VARCHAR(100) , lastName VARCHAR(100) , address VARCHAR(100) , city VARCHAR(100) , state VARCHAR(50) , zip VARCHAR(20) , departmentName VARCHAR(20) , startDate DATETIME NOT NULL , endDate DATETIME NOT NULL , PERIOD FOR appl_time (startDate, endDate) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING;
This is all that is required for MariaDB to begin transparently capturing copies of rows in the Employees table as they are modified. If Georgi moves to a new address we can keep track of the changes.
UPDATE Employees SET address = '239 Rutherford Ave.' WHERE empId = 2;
Simple queries can now be used to inspect all the records in the table: SELECT firstName , lastName , address , departmentName , startDate , row_start FROM Employees FOR SYSTEM_TIME ALL WHERE empID = 2 ORDER BY row_start;
The output below shows three rows were added with the new address, one for each time interval in startDate (used for the application time). The row_start column can now be used for audits or to retrieve the row at a particular point-in-time.
In summary, the most important characteristic of temporal extensions in MariaDB is that they are implemented according to the specification in the SQL:2011 standard. Additional information is available from the Temporal Features in SQL:2011, by Michels Kulkarni.
In this blog series, we covered many features of temporal tables implemented in MariaDB Platform, such as:
- Support of system time versioned tables
- Management of historical data growth
- Defining and using application time
- Combining system time and application time with bitemporal tables
The features presented in this series, are very helpful to perform data audits, point in time analysis (time travel), anomaly detection or be used to implement slowly-changing dimensions.