Temporal Tables Part 4: Application Time

This is part 4 of a 5-part series, if you want to start from the beginning see Temporal Tables Part 1: Introduction & Use Case Example

The preceding parts of this series introduced system time and transaction precise time. It showed how MariaDB can automatically capture temporal information based on those definitions.  However, application defined timing information is often more important.  Often times it is less important to know when a change was recorded in the system, than it is to know when that change is meant to take effect.  Let’s take a Human Resource Management System (HRMS) and a simplified view of how employee information is tracked.

CREATE TABLE Employees (
    empID               INTEGER
  , firstName           VARCHAR(100)
  , lastName            VARCHAR(100)
  , address_1           VARCHAR(100)
  , city                VARCHAR(100)
  , state               VARCHAR(50)
  , zip                 VARCHAR(20)
  , departmentName      VARCHAR(20) 
  , startDate           DATETIME NOT NULL
  , endDate             DATETIME NOT NULL
);

The startDate and endDate columns define the period during which a particular entry is valid.  Application logic then uses standard SQL to query for a specific point in time.

SELECT *
FROM Employees
WHERE startDate >='2017-01-01 00:00:00'
  AND endDate < '2017-01-01 23:59:59';

The 10.4 release of MariaDB Server has added additional support these application time definitions.  The DDL statement can now declare that the startDate and endData columns work together to define a time period.

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)
);

The benefit of declaring the time period comes when performing deletes or updates that can take advantage of the FOR PORTION OF syntax to make changes that respect period definitions.

Let’s take an example where we want to make some retroactive changes to employee’s titles.  First let’s load for a single employee.

INSERT INTO Employees VALUES
(1, 'John', 'Smith', 'Sales', '2015-01-01', '2018-12-31');

Imagine we want to record that John has been assigned to the marketing department from 2017-01-01 to 2017-06-30.  Without application time period support, this would involve individual SQL statements to update the endDate on one row, the startDate on another row, and a statement to insert a new row.  With MariaDB application time support and the FOR PORTION OF syntax, this can be accomplished with a single statement.

UPDATE Employees
  FOR PORTION OF appl_time
FROM '2016-01-01' to '2016-06-30'
  SET departmentName = 'Marketing'
WHERE empId = 1;

The application can query the data using the below SELECT statement: 

SELECT *
FROM Employees
WHERE empId = 1
ORDER BY startDate;

To retrieve the different departments John has been working over different periods of time:

Similar syntax can be used to delete information.  In this case, let’s assume we have learned that John was not employed by the company for a portion of the time that we currently have him assigned to marketing.

DELETE from test.Employees
FOR PORTION OF appl_time
FROM '2016-02-01' to '2016-04-30';

Like before a simple SELECT statement is all you need:

SELECT *
FROM Employees
WHERE empId = 1
ORDER BY startDate;

Now we can see the period of time John has not been working for the company is reflected in the output:

While somewhat simplified, the above examples illustrate how making changes to application time are much easier when using the new syntax available MariaDB 10.4.  Working with application time will get even easier with subsequent releases, as there as future enhancements planned  These include additional syntax for querying time periods (MDEV-16976), and enforcement of non-overlapping time periods via an integrity constraint (MDEV-16978).

Things get really interesting when you combine system time and application time. Continue to Temporal Tables Part 5: Bitemporal Tables and Queries to learn more.