Temporal Tables

In addition to classic date, time, and date/time temporal data types, MariaDB supports three modern (SQL:2011) types of temporal data.

Type

Tracks

Sample Use Cases

System-Versioned

Change history

Audit, forensics, IoT temperature tracking

Application-Time Period

Time-limited values

Sales offers, subscriptions

Bitemporal

Time-limited values with history

Schedules, decision support models

MariaDB Enterprise Server 10.4 supports system-versioning, application-time periods, and bitemporal tables. MariaDB Enterprise Server 10.3 supports system-versioned tables only. MariaDB Enterprise Server 10.2 does not support temporal tables.

System-Versioned Tables

Normally, when you issue a statement that updates a row on a table, the new values replace the old values on the row so that only the most current data remains available to the application.

With system-versioned tables, MariaDB Server tracks the points in time when rows change. When you update a row on these tables, it creates a new row to display as current without removing the old data. This tracking remains transparent to the application. When querying a system-versioned table, you can retrieve either the most current values for every row or the historic values available at a given point in time.

You may find this feature useful in efficiently tracking the time of changes to continuously-monitored values that do not change frequently, such as changes in temperature over the course of a year. System versioning is often useful for auditing.

Applying System-Versioning

You can add system-versioning to a table when you create the table or afterward by modifying the table. You can also apply system-versioning only to specific columns.

To enable system-versioning, use a CREATE TABLE statement such as:

CREATE TABLE accounts (
       id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(255),
       amount INT
) WITH SYSTEM VERSIONING;

In this example we create a table for tracking accounts. The WITH SYSTEM VERSIONING clause initializes hidden, system-managed row_start and row_end columns for tracking changes by row.

To add system-versioning to an existing table, use the ALTER TABLE statement.

First, create a table without system-versioning:

CREATE TABLE accounts_alt (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(255),
   amount INT
);

Then, use ALTER TABLE to add system-versioning:

ALTER TABLE accounts_alt ADD SYSTEM VERSIONING;

System-Versioned Columns

Enabling system-versioning for an entire table will record timestamps when any column is changed. This may not always be desirable. To record changes to specific columns, set the system-versioning for only those columns.

CREATE TABLE accounts_col (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(255),
   amount INT WITH SYSTEM VERSIONING);

Here, the table only records the changes when you modify the designated column. Changes made to un-versioned columns occur normally, as though the table did not use any system-versioning.

Removing System-Versioning

To remove system-versioning from a table, use an ALTER TABLE statement:

ALTER TABLE accounts_alt DROP SYSTEM VERSIONING;

Querying System-Versioning

When issuing queries against system-versioned tables, there are several new features in MariaDB SQL to control access to historic data.

To start, add some data on the accounts table created above:

INSERT INTO accounts (name, amount)
VALUES ("Smith", 400),
       ("Orson", 300),
       ("Serio", 500),
       ("Wallace", 200),
       ("March", 600),
       ("Douglas", 100);

Then, update one or more of the rows:

UPDATE accounts
SET amount = 1000
WHERE id = 1
   OR id = 3;

Retrieve Current Data

Normal queries issued against system-versioned tables always default to returning the most current data available.

SELECT name, amount FROM accounts
WHERE amount > 900;

+--------+--------+
| name   | amount |
+--------+--------+
|  Smith |   1000 |
|  Serio |   1000 |
+--------+--------+

To make the system-versioning columns visible, add the row_start and row_end columns to the query:

SELECT *, row_start, row_end FROM accounts;

+----+---------+--------+----------------------------+----------------------------+
| id | name    | amount | row_start                  | row_end                    |
+----+---------+--------+----------------------------+----------------------------+
|  1 | Smith   |   1000 | 2019-07-06 15:38:04.516745 | 2038-01-18 22:14:07.999999 |
|  2 | Orson   |    300 | 2019-07-06 15:37:53.477688 | 2038-01-18 22:14:07.999999 |
|  3 | Serio   |   1000 | 2019-07-06 15:38:04.516745 | 2038-01-18 22:14:07.999999 |
|  4 | Wallace |    200 | 2019-07-06 15:37:53.477688 | 2038-01-18 22:14:07.999999 |
|  5 | March   |    600 | 2019-07-06 15:37:53.477688 | 2038-01-18 22:14:07.999999 |
|  6 | Douglas |    100 | 2019-07-06 15:37:53.477688 | 2038-01-18 22:14:07.999999 |
+----+---------+--------+----------------------------+----------------------------+

Retrieve Historical Data

System-versioning enables several new SQL features for accessing historic data. These statements all begin with the FOR SYSTEM TIME clause. To view all current rows with all historic data, use the ALL keyword:

SELECT name, amount FROM accounts
FOR SYSTEM_TIME ALL;

More commonly, you might want to access rows visible at a specific point in the past:

SELECT name, amount FROM accounts
FOR SYSTEM_TIME AS OF '2019-06-18 11:00';

Alternatively, accessing all rows visible within a certain period:

SELECT name, amount FROM accounts
FOR SYSTEM_TIME
   BETWEEN (NOW() - INTERVAL 1 YEAR)
   AND NOW();

Rows changed during this period show as duplicate entries for the Primary Key. This is because system-versioned tables include the timestamp columns with the Primary Key when testing uniqueness.

This query displays all rows from a specified time period.

SELECT name, amount FROM accounts
FOR SYSTEM_TIME
   FROM '2019-06-01 11:00'
   TO   '2019-06-30 11:00';

These statements also work in subqueries and with views.

CREATE VIEW amount AS
SELECT * FROM accounts;

SELECT name, amount FROM amount
FOR SYSTEM_TIME ALL;

Deletion and Truncation

When you delete a row from a system-versioned table, the historic data is not removed from the table. Queries for current data will exclude the deleted row(s) whereas queries for historic data will include the deleted row(s).

To protect historic data, the TRUNCATE statement raises Error 4137.

TRUNCATE accounts;
Error 4137 (HY000): System-versioned tables do not support TRUNCATE TABLE

To prune the history of a table (in this case two records), use DELETE HISTORY.

DELETE HISTORY FROM accounts;

Use system-versioning to delete historical data before a specified date:

DELETE HISTORY FROM accounts
BEFORE SYSTEM_TIME '2019-06-11 12:00';

Application-Time Period Tables

With system-versioned tables, MariaDB Platform creates and maintains an internal time period for tracking changes to the table at a system-level. MariaDB Platform also supports the use of time periods at the application-level. Here, the user creates a table with two temporal columns that the application then uses to determine when the row is in effect.

For example, this might be useful in determining if a given promotion or subscription is currently active.

Applying Application-Time Periods

You can add application-time periods to a table when you create the table or afterward by modifying the table to add support for time period operations.

To create a table with a time period, set two columns with temporal data types (such as DATE or TIMESTAMP, but not TIME, which is not supported), then provide a period specification using these columns.

CREATE TABLE coupons (
   id INT UNSIGNED ,
   name VARCHAR(255) DEFAULT 'Daily Special',
   date_start DATE,
   date_end DATE,
   PERIOD FOR valid_period(date_start, date_end)
);

Alternatively, you can add a time period to an existing table. First, create a table with the relevant temporal columns:

CREATE TABLE coupons_alt (
   id INT UNSIGNED,
   name VARCHAR(255),
   date_start DATE,
   date_end DATE);

Then, use an ALTER TABLE statement to add the time period:

ALTER TABLE coupons_alt
ADD PERIOD FOR valid_period(date_start, date_end);

Removing Application-Time Periods

To remove an application time period, use DROP PERIOD:

ALTER TABLE coupons_alt
DROP PERIOD FOR valid_period

Operating by Portion

Application-time period tables support UPDATE and DELETE operations by portion of the row's time period.

Updating by Portion

Using an UPDATE FOR PORTION statement, you can modify rows with periods that fall within that of the given portion. The statement modifies all rows that fall completely within the portions. Rows that overlap with the portion are split, with the changes applied to the relevant portion only.

This statement cannot modify or reference the temporal columns used by the time period. It also does not support operating on multiple portions in a single statement.

First, remove all data from the test table:

TRUNCATE coupons;

Then, use an INSERT statement to add data to operate on:

INSERT INTO coupons(id, date_start, date_end)
VALUES (1, '2019-05-03', '2019-05-14'),
       (2, '2019-05-20', '2019-06-14'),
       (3, '2019-04-01', '2019-05-30'),
       (4, '2019-04-01', '2019-06-12');

For reference purposes, query the starting value with a SELECT statement:

SELECT * FROM coupons;

+------+---------------+------------+------------+
| id   | name          | date_start | date_end   |
+------+---------------+------------+------------+
|    1 | Daily Special | 2019-05-03 | 2019-05-14 |
|    2 | Daily Special | 2019-05-20 | 2019-06-14 |
|    3 | Daily Special | 2019-04-01 | 2019-05-30 |
|    4 | Daily Special | 2019-04-01 | 2019-06-12 |
+------+---------------+------------+------------+

To update by time period, use the FOR PORTION clause:

UPDATE coupons FOR PORTION OF valid_period
   FROM '2019-06-01'
     TO '2019-06-30'
SET name = "June Special";
SELECT * FROM coupons;

+------+---------------+------------+------------+
| id   | name          | date_start | date_end   |
+------+---------------+------------+------------+
|    1 | Daily Special | 2019-05-03 | 2019-05-14 |
|    2 | June Special  | 2019-06-01 | 2019-06-14 |
|    3 | Daily Special | 2019-04-01 | 2019-05-30 |
|    4 | June Special  | 2019-06-01 | 2019-06-12 |
|    2 | Daily Special | 2019-05-20 | 2019-06-01 |
|    4 | Daily Special | 2019-04-01 | 2019-06-01 |
+------+---------------+------------+------------+

Notice that row 1 was ignored, since it falls within the month of May. Rows 2 and 4 have periods in June so they were split: the June portion given the update while the remainder put into a new row. Row 3 was ignored, as it falls within April and May.

Deleting by Portion

Using the DELETE FOR PORTION, you can remove rows with periods that fall within that of the given portion. The statement removes rows that fall completely within the portion, shrinks rows that overlap with the portion, and splits rows that encompass the portion.

This statement does not support deletion by multiple portions in a single statement.

First, remove all data from the test table:

TRUNCATE coupons;

Then, use an INSERT statement to add data to operate on:

INSERT INTO coupons(id, date_start, date_end)
VALUES (1, '2019-05-03', '2019-05-14'),
       (2, '2019-05-20', '2019-06-14'),
       (3, '2019-04-01', '2019-05-30'),
       (4, '2019-04-01', '2019-06-12');

For reference purposes, query the starting value with a SELECT statement:

SELECT * FROM coupons;

+------+---------------+------------+------------+
| id   | name          | date_start | date_end   |
+------+---------------+------------+------------+
|    1 | Daily Special | 2019-05-03 | 2019-05-14 |
|    2 | Daily Special | 2019-05-20 | 2019-06-14 |
|    3 | Daily Special | 2019-04-01 | 2019-05-30 |
|    4 | Daily Special | 2019-04-01 | 2019-06-12 |
+------+---------------+------------+------------+

To remove rows by time period, use the FOR PORTION clause:

DELETE FROM coupons FOR PORTION OF valid_period
   FROM '2019-05-01'
     TO '2019-05-31';

Issue another SELECT statement to view the results:

SELECT * FROM coupons;

+------+---------------+------------+------------+
| id   | name          | date_start | date_end   |
+------+---------------+------------+------------+
|    2 | Daily Special | 2019-05-31 | 2019-06-14 |
|    3 | Daily Special | 2019-04-01 | 2019-05-01 |
|    4 | Daily Special | 2019-04-01 | 2019-05-01 |
|    4 | Daily Special | 2019-05-31 | 2019-06-12 |
+------+---------------+------------+------------+

Notice that row 1 was completely removed as its period occurs within the month of May. The periods for rows 2 and 3 were shrunk to exclude days in May, excepting the 1st and 31st, which defined the portion. Row 4, which ran from April to June was split into separate rows for its April and June components.

Bitemporal Tables

If your application needs to utilize both system-versioning and application-time periods, you can combine them in bitemporal tables. The earlier instructions for maintaining each type of temporal data applies.

CREATE TABLE coupons_new (
   id INT UNSIGNED,
   name VARCHAR(255),
   date_start DATE,
   date_end DATE,
   PERIOD FOR valid_period(date_start, date_end)
) WITH SYSTEM VERSIONING;

This creates a table with two time periods, the arbitrary one based on the two DATE columns for application-time periods, and the internal SYSTEM_TIME for system-versioning.

You may find this useful in applications where you need an audit trail for data change while still needing to perform operations dependent on application-time periods.

Backing up Temporal Tables

MariaDB Enterprise Backup copies the data directory from the running MariaDB Enterprise Server and is the recommended backup tool for temporal tables. It is able to backup and restore temporal tables without issue.

By comparison, mysqldump does not always include the hidden row_start and row_end temporal columns in the backup, which can sometimes cause the restore operation to fail.

MariaDB Replication

When using system-versioned tables with MariaDB Replication, always set the secure_timestamp system variable to YES on Replica Servers. This ensures that MariaDB Enterprise Server uses its own system clock in system-versioning values.

As these columns are used in forming the Primary Key, it allows the Primary Server to retry replication without causing conflicts.

Data Retention Strategies

Each INSERT or UPDATE to a system-versioned table increases the table's size as every row must be retained. For large tables receiving many writes, this can gradually fill available disk space. There are several strategies to consider to keep disk requirements manageable.

Partitions and Sharding

Using system-versioning with a table partitioned by SYSTEM_TIME, MariaDB Enterprise Server writes historic data to separate partitions from current data.

When combined with the Spider storage engine, MariaDB Enterprise Server shards historic data across multiple Servers, enabling storage scale-out.

Delete History

When older history is not important, you can use the DELETE HISTORY statement.

Replace System-Versioning

If data retention is not needed for auditing or other mission-critical purposes, you can periodically remove all historic data by dropping system versioning from the table and then adding it back:

ALTER TABLE accounts DROP SYSTEM VERSIONING;

ALTER TABLE accounts ADD SYSTEM VERSIONING;

This removes all historic data and history tracking begins anew with the current rows.

Reference

Additional information available from the Temporal Features in SQL:2011, by Michels Kulkarni.