MariaDB supports temporal data tables in the form of system-versioning tables
(allowing you to query and operate on historic data, discussed below),
application-time periods (allow you to query and operate on a temporal range
of data), and bitemporal tables (which combine both system-versioning and
application-time periods).

System-Versioned Tables
-----------------------

System-versioned tables store the history of all changes, not only data which
is currently valid. This allows data analysis for any point in time, auditing
of changes and comparison of data from different points in time. Typical uses
cases are:

* Forensic analysis & legal requirements to store data for N years.
* Data analytics (retrospective, trends etc.), e.g. to get your staff
information as of one year ago.
* Point-in-time recovery - recover a table state as of particular point in
time.

System-versioned tables were first introduced in the SQL:2011 standard.

Creating a System-Versioned Table
---------------------------------

The CREATE TABLE syntax has been extended to permit creating a
system-versioned table. To be system-versioned, according to SQL:2011, a table
must have two generated columns, a period, and a special table option clause:

CREATE TABLE t(
 x INT,
 start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
 end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
 PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)
) WITH SYSTEM VERSIONING;

In MariaDB one can also use a simplified syntax:

CREATE TABLE t (
 x INT
) WITH SYSTEM VERSIONING;

In the latter case no extra columns will be created and they won't clutter the
output of, say, SELECT * FROM t. The versioning information will still be
stored, and it can be accessed via the pseudo-columns ROW_START and ROW_END:

SELECT x, ROW_START, ROW_END FROM t;

Adding or Removing System Versioning To/From a Table
----------------------------------------------------

An existing table can be altered to enable system versioning for it.

CREATE TABLE t(
 x INT
);

ALTER TABLE t ADD SYSTEM VERSIONING;

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
   Table: t
Create Table: CREATE TABLE `t` (
 `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

Similarly, system versioning can be removed from a table:

ALTER TABLE t DROP SYSTEM VERSIONING;

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
   Table: t
Create Table: CREATE TABLE `t` (
 `x` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

One can also add system versioning with all columns created explicitly:

ALTER TABLE t ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
       ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
       ADD PERIOD FOR SYSTEM_TIME(ts, te),
       ADD SYSTEM VERSIONING;

SHOW CREATE TABLE t\G
*************************** 1. row ***************************
   Table: t
Create Table: CREATE TABLE `t` (
 `x` int(11) DEFAULT NULL,
 `ts` timestamp(6) GENERATED ALWAYS AS ROW START,
 `te` timestamp(6) GENERATED ALWAYS AS ROW END,
 PERIOD FOR SYSTEM_TIME (`ts`, `te`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING

Inserting Data
--------------

When data is inserted into a system-versioned table, it is given a row_start
value of the current timestamp, and a row_end value of
FROM_UNIXTIME(2147483647.999999). The current timestamp can be adjusted by
setting the timestamp system variable, for example:

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-10-24 23:09:38 |
+---------------------+

INSERT INTO t VALUES(1);


INSERT INTO t VALUES(2);


INSERT INTO t VALUES(3);

SELECT a,row_start,row_end FROM t;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:09:38.951347 | 2038-01-19 05:14:07.999999 |
|    2 | 2033-10-24 00:00:00.000000 | 2038-01-19 05:14:07.999999 |
|    3 | 2022-10-24 23:09:38.961857 | 2038-01-19 05:14:07.999999 |
+------+----------------------------+----------------------------+

Querying Historical Data
------------------------

SELECT
------

To query the historical data one uses the clause FOR SYSTEM_TIME directly
after the table name (before the table alias, if any). SQL:2011 provides three
syntactic extensions:

* AS OF is used to see the table as it was at a specific point in time in the
past:

SELECT * FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';

* BETWEEN start AND end will show all rows that were visible at any point
between two specified points in time. It works inclusively, a row visible
exactly at start or exactly at end will be shown too.

SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW();

* FROM start TO end will also show all rows that were visible at any point
between two specified points in time, including start, but excluding end.

SELECT * FROM t FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01
00:00:00';

Additionally MariaDB implements a non-standard extension:

* ALL will show all rows, historical and current.

SELECT * FROM t FOR SYSTEM_TIME ALL;
