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;

If the FOR SYSTEM_TIME clause is not used, the table will show the current
data. This is usually the same as if one had specified FOR SYSTEM_TIME AS OF
CURRENT_TIMESTAMP, unless one has adjusted the row_start value (until MariaDB
10.11, only possible by setting the secure_timestamp variable). For example:

CREATE OR REPLACE TABLE t (a int) WITH SYSTEM VERSIONING;

SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2022-10-24 23:43:37 |
+---------------------+

INSERT INTO t VALUES (1);

SET @@timestamp = UNIX_TIMESTAMP('2033-03-03');

INSERT INTO t VALUES (2);

DELETE FROM t;

SET @@timestamp = default;

SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME ALL;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
|    2 | 2033-03-03 00:00:00.000000 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
2 rows in set (0.000 sec)

SELECT a, row_start, row_end FROM t FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP;
+------+----------------------------+----------------------------+
| a    | row_start                  | row_end                    |
+------+----------------------------+----------------------------+
|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |
+------+----------------------------+----------------------------+
1 row in set (0.000 sec)

SELECT a, row_start, row_end FROM t;
Empty set (0.001 sec)

Views and Subqueries
--------------------

When a system-versioned tables is used in a view or in a subquery in the from
clause, FOR SYSTEM_TIME can be used directly in the view or subquery body, or
(non-standard) applied to the whole view when it's being used in a SELECT:

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

Or

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

Use in Replication and Binary Logs
----------------------------------

Tables that use system-versioning implicitly add the row_end column to the
Primary Key. While this is generally not an issue for most use cases, it can
lead to problems when re-applying write statements from the binary log or in
replication environments, where a primary retries an SQL statement on the
replica.

Specifically, these writes include a value on the row_end column containing
the timestamp from when the write was initially made. The re-occurrence of the
Primary Key with the old system-versioning columns raises an error due to the
duplication.

To mitigate this with MariaDB Replication, set the secure_timestampá¬öû