Description\n-----------\n\nMariaDB 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).\n\nSystem-Versioned Tables\n\nSystem-versioned tables store the history of all changes, not only data which is currently applicable. 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:\n\n Forensic analysis & legal requirements to store data for N years.\n Data analytics (retrospective, trends etc.), e.g. to get your staff information as of one year ago.\n Point-in-time recovery - recover a table state as of particular point in time.\n\nSystem-versioned tables were first introduced in the SQL:2011 standard.\n\nThe keyword SYSTEM is used in the SYSTEM VERSIONING clause. Using SYSTEM as an unquoted column name in an ALTER TABLE is not permitted by some parser rules. Even though SYSTEM is not a fully reserved word in Oracle mode, the ADD statement will result in a syntax error.\n\nAlways provide SYSTEM when using it as a column name: system. See the Reserved Words page for additional examples and a detailed explanation.\n\nCreating a System-Versioned Table\n\nThe 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:\n\n``sql\nCREATE TABLE t(\n   x INT,\n   start_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW START,\n   end_timestamp TIMESTAMP(6) GENERATED ALWAYS AS ROW END,\n   PERIOD FOR SYSTEM_TIME(start_timestamp, end_timestamp)\n) WITH SYSTEM VERSIONING;\n`\n\nIn MariaDB, you can use a simplified syntax:\n\n`sql\nCREATE TABLE t (\n   x INT\n) WITH SYSTEM VERSIONING;\n`\n\nIn 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:\n\n`sql\nSELECT x, ROW_START, ROW_END FROM t;\n`\n\nAdding or Removing System Versioning To/From a Table\n\nAn existing table can be altered to enable system versioning for it.\n\n`sql\nCREATE TABLE t(\n  x INT\n);\n`\n\n`sql\nALTER TABLE t ADD SYSTEM VERSIONING;\n`\n\n`sql\nSHOW CREATE TABLE t\\G\n************************ 1. row *********************\n       Table: t\nCreate Table: CREATE TABLE t (\n  x int(11) DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING\n`\n\nSimilarly, system versioning can be removed from a table:\n\n`sql\nALTER TABLE t DROP SYSTEM VERSIONING;\n`\n\n`sql\nSHOW CREATE TABLE t\\G\n********************* 1. row *********************\n       Table: t\nCreate Table: CREATE TABLE t (\n  x int(11) DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\n`\n\nYou can also add system versioning with all columns created explicitly:\n\n`sql\nALTER TABLE t ADD COLUMN ts TIMESTAMP(6) GENERATED ALWAYS AS ROW START,\n              ADD COLUMN te TIMESTAMP(6) GENERATED ALWAYS AS ROW END,\n              ADD PERIOD FOR SYSTEM_TIME(ts, te),\n              ADD SYSTEM VERSIONING;\n`\n\n`sql\nSHOW CREATE TABLE t\\G\n********************* 1. row *********************\n       Table: t\nCreate Table: CREATE TABLE t (\n  x int(11) DEFAULT NULL,\n  ts timestamp(6) GENERATED ALWAYS AS ROW START,\n  te timestamp(6) GENERATED ALWAYS AS ROW END,\n  PERIOD FOR SYSTEM_TIME (ts, te)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1 WITH SYSTEM VERSIONING\n`\n\nMariaDB starting with 11.7\n\nIt is possible to convert a versioned table from implicit to explicit row_start/row_end columns. Note that, in order to do any ALTER on a system versioned table, system_versioning_alter_history must be set to KEEP.\n\n`sql\nCREATE OR REPLACE TABLE t1 (x INT) WITH SYSTEM VERSIONING;\n\nSET system_versioning_alter_history = keep;\n\nALTER TABLE t1 ADD COLUMN rs TIMESTAMP(6) AS ROW START, \n  ADD COLUMN re TIMESTAMP(6) AS ROW END, ADD PERIOD FOR SYSTEM_TIME (rs,re)\n`\n\nIt is not possible to convert a versioned table from implicit to explicit row_start/row_end columns. Doing so results in a duplicate row error:\n\n`sql\nCREATE OR REPLACE TABLE t1 (x INT) WITH SYSTEM VERSIONING;\n\nSET system_versioning_alter_history = keep;\n\nALTER TABLE t1 ADD COLUMN rs TIMESTAMP(6) AS ROW START, \n  ADD COLUMN re TIMESTAMP(6) AS ROW END, ADD PERIOD FOR SYSTEM_TIME (rs,re);\n\nERROR 4134 (HY000): Duplicate ROW START column rs\n`\n\nInserting Data\n\nWhen 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:\n\n`sql\nSELECT NOW();\n+---------------------+\n| NOW()               |\n+---------------------+\n| 2022-10-24 23:09:38 |\n+---------------------+\n \nINSERT INTO t VALUES(1);\n \nSET @@timestamp = UNIX_TIMESTAMP('2033-10-24');\n\nINSERT INTO t VALUES(2);\n \nSET @@timestamp = default;\n\nINSERT INTO t VALUES(3);\n \nSELECT a,row_start,row_end FROM t;\n+------+----------------------------+----------------------------+\n| a    | row_start                  | row_end                    |\n+------+----------------------------+----------------------------+\n|    1 | 2022-10-24 23:09:38.951347 | 2038-01-19 05:14:07.999999 |\n|    2 | 2033-10-24 00:00:00.000000 | 2038-01-19 05:14:07.999999 |\n|    3 | 2022-10-24 23:09:38.961857 | 2038-01-19 05:14:07.999999 |\n+------+----------------------------+----------------------------+\n`\n\nQuerying Historical Data\n\nSELECT\n\nTo 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:\n\n AS OF is used to see the table as it was at a specific point in time in the past:\n\n`sql\nSELECT  FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';\n`\n\n 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.\n\n`sql\nSELECT  FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 YEAR) AND NOW();\n`\n\n 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.\n\n`sql\nSELECT  FROM t FOR SYSTEM_TIME FROM '2016-01-01 00:00:00' TO '2017-01-01 00:00:00';\n`\n\nAdditionally, MariaDB implements a non-standard extension.\n\n ALL will show all rows, historical and current.\n\n`sql\nSELECT  FROM t FOR SYSTEM_TIME ALL;\n`\n\nIf the FOR SYSTEM_TIME clause is not used, the table shows the _current_ data. This is usually the same as if you had specified FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP, unless you've adjusted the _row_start_ value:\n\n`sql\nCREATE OR REPLACE TABLE t (a int) WITH SYSTEM VERSIONING;\n\nSELECT NOW();\n+---------------------+\n| NOW()               |\n+---------------------+\n| 2022-10-24 23:43:37 |\n+---------------------+\n\nINSERT INTO t VALUES (1);\n\nSET @@timestamp = UNIX_TIMESTAMP('2033-03-03');\n\nINSERT INTO t VALUES (2);\n\nDELETE FROM t;\n\nSET @@timestamp = default;\n\nSELECT a, row_start, row_end FROM t FOR SYSTEM_TIME ALL;\n+------+----------------------------+----------------------------+\n| a    | row_start                  | row_end                    |\n+------+----------------------------+----------------------------+\n|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |\n|    2 | 2033-03-03 00:00:00.000000 | 2033-03-03 00:00:00.000000 |\n+------+----------------------------+----------------------------+\n2 rows in set (0.000 sec)\n\nSELECT a, row_start, row_end FROM t FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP;\n+------+----------------------------+----------------------------+\n| a    | row_start                  | row_end                    |\n+------+----------------------------+----------------------------+\n|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |\n+------+----------------------------+----------------------------+\n1 row in set (0.000 sec)\n\nSELECT a, row_start, row_end FROM t;\nEmpty set (0.001 sec)\n`\n\nIf the FOR SYSTEM_TIME clause is not used, the table shows the _current_ data. This is usually the same as if you had specified FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP, unless you've adjusted the _row_start_ value (only possible by setting the secure_timestamp variable):\n\n`sql\nCREATE OR REPLACE TABLE t (a int) WITH SYSTEM VERSIONING;\n\nSELECT NOW();\n+---------------------+\n| NOW()               |\n+---------------------+\n| 2022-10-24 23:43:37 |\n+---------------------+\n\nINSERT INTO t VALUES (1);\n\nSET @@timestamp = UNIX_TIMESTAMP('2033-03-03');\n\nINSERT INTO t VALUES (2);\n\nDELETE FROM t;\n\nSET @@timestamp = default;\n\nSELECT a, row_start, row_end FROM t FOR SYSTEM_TIME ALL;\n+------+----------------------------+----------------------------+\n| a    | row_start                  | row_end                    |\n+------+----------------------------+----------------------------+\n|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |\n|    2 | 2033-03-03 00:00:00.000000 | 2033-03-03 00:00:00.000000 |\n+------+----------------------------+----------------------------+\n2 rows in set (0.000 sec)\n\nSELECT a, row_start, row_end FROM t FOR SYSTEM_TIME AS OF CURRENT_TIMESTAMP;\n+------+----------------------------+----------------------------+\n| a    | row_start                  | row_end                    |\n+------+----------------------------+----------------------------+\n|    1 | 2022-10-24 23:43:37.192725 | 2033-03-03 00:00:00.000000 |\n+------+----------------------------+----------------------------+\n1 row in set (0.000 sec)\n\nSELECT a, row_start, row_end FROM t;\nEmpty set (0.001 sec)\n`\n\nViews and Subqueries\n\nWhen a system-versioned table 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:\n\n`sql\nCREATE VIEW v1 AS SELECT  FROM t FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';\n`\n\nOr:\n\n`sql\nCREATE VIEW v1 AS SELECT  FROM t;\nSELECT  FROM v1 FOR SYSTEM_TIME AS OF TIMESTAMP'2016-10-09 08:07:06';\n`\n\nUse in Replication and Binary Logs\n\nTables 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 master retries an SQL statement on the slave.\n\nSpecifically, 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.\n\nTo mitigate this with MariaDB Replication, set the secure_timestamp system variable to YES on the slave. When set, the slave uses its own system clock when applying to the row log, meaning that the primary can retry as many times as needed without causing a conflict. The retries generate new historical rows with new values for the row_start and row_end columns.\n\nTransaction-Precise History in InnoDB\n\nA point in time when a row was inserted or deleted does not necessarily mean that a change became visible at the same moment. With transactional tables, a row might have been inserted in a long transaction, and became visible hours after it was inserted.\n\nFor some applications — for example, when doing data analytics on one-year-old data — this distinction does not matter much. For others — forensic analysis — it might be crucial.\n\nMariaDB supports transaction-precise history (only for the InnoDB storage engine) that allows seeing the data exactly as it would've been seen by a new connection doing a SELECT at the specified point in time — rows inserted _before_ that point, but committed _after_ will not be shown.\n\nTo use transaction-precise history, InnoDB needs to remember not timestamps, but transaction identifier per row. This is done by creating generated columns as BIGINT UNSIGNED, not TIMESTAMP(6):\n\n`sql\nCREATE TABLE t(\n   x INT,\n   start_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW START,\n   end_trxid BIGINT UNSIGNED GENERATED ALWAYS AS ROW END,\n   PERIOD FOR SYSTEM_TIME(start_trxid, end_trxid)\n) WITH SYSTEM VERSIONING;\n`\n\nThese columns must be specified explicitly, but they can be made INVISIBLE to avoid cluttering SELECT  output.\n\nNote that if you are using an engine that does not support system versioning with transaction ids, you will get an error like "start_trxid must be of type TIMESTAMP(6) for system-versioned table t".\n\nWhen one uses transaction-precise history, one can optionally use transaction identifiers in the FOR SYSTEM_TIME clause:\n\n`sql\nSELECT  FROM t FOR SYSTEM_TIME AS OF TRANSACTION 12345;\n`\n\nThis shows the data, exactly as it was seen by the transaction with the identifier 12345.\n\nData for this feature is stored in the mysql.transaction_registry table.\n\nLimitations\n\nPARTITION BY SYSTEM_TIME is not supported when using transaction-precise system versioning. Transaction-precise history tracks row versions using transaction identifiers instead of timestamps, so, SYSTEM_TIME cannot be used to partition transaction-precise system history. If partitioning is required, use the default timestamp-based versioning.\n\nStoring the History Separately\n\nWhen the history is stored together with the current data, it increases the size of the table, so current data queries — table scans and index searches — will take more time, because they will need to skip over historical data. If most queries on that table use only current data, it might make sense to store the history separately, to reduce the overhead from versioning.\n\nThis is done by partitioning the table by SYSTEM_TIME. Because of the partition pruning optimization, all current data queries will only access one partition, the one that stores current data.\n\nThis example shows how to create such a partitioned table:\n\n`sql\nCREATE TABLE t (x INT) WITH SYSTEM VERSIONING\n  PARTITION BY SYSTEM_TIME (\n    PARTITION p_hist HISTORY,\n    PARTITION p_cur CURRENT\n  );\n`\n\nIn this example, all history will be stored in the partition p_hist while all current data will be in the partition p_cur. The table must have exactly one current partition and at least one historical partition.\n\nPartitioning by SYSTEM_TIME also supports automatic partition rotation. You can rotate historical partitions by time or by size. This example shows how to rotate partitions by size:\n\n`sql\nCREATE TABLE t (x INT) WITH SYSTEM VERSIONING\n  PARTITION BY SYSTEM_TIME LIMIT 100000 (\n    PARTITION p0 HISTORY,\n    PARTITION p1 HISTORY,\n    PARTITION pcur CURRENT\n  );\n`\n\nMariaDB starts writing history rows into partition p0, and at the end of the statement that wrote the 100000th row, MariaDB will s\n\n[Truncated — full content at mariadb.com/docs]