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

Part 4: Application Time

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:

Temporal table: example 1

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;

Temporal table: Example 2

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.

Temporal table: Example 3

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.

Â