12. Temporal tables: system versioning and application-time periods

Miraj offers two temporal dimensions, separately or together (bitemporal tables):

• system versioning (WITH SYSTEM VERSIONING) keeps the history of the rows: an UPDATE or a DELETE preserves the old version, along with the time it ceased to be current. You can then answer "what was the value on December 31?" without a hand-written audit log;
• application-time periods (PERIOD FOR) describe a business validity (a price valid from January 1 to March 31, a contract, an assignment) that the application writes itself, with FOR PORTION OF to change only part of a period and WITHOUT OVERLAPS to forbid overlaps.

12.1 Creating a versioned table#

CREATE TABLE stock (
    article INT PRIMARY KEY,
    qte     INT NOT NULL
) WITH SYSTEM VERSIONING;

Miraj adds two invisible TIMESTAMP(6) columns, row_start and row_end, which delimit the validity period of each version, to the microsecond. SELECT * does not show them, an INSERT without a column list gives them no value, and SHOW CREATE TABLE and DESCRIBE omit them; you refer to them by name:

INSERT INTO stock VALUES (12, 40), (13, 8);
SELECT article, qte, row_start, row_end FROM stock;

+---------+-----+----------------------------+----------------------------+
| article | qte | row_start                  | row_end                    |
+---------+-----+----------------------------+----------------------------+
|      12 |  40 | 2026-10-19 03:33:14.747306 | 2038-01-19 03:14:07.999999 |
|      13 |   8 | 2026-10-19 03:33:14.747306 | 2038-01-19 03:14:07.999999 |
+---------+-----+----------------------------+----------------------------+

A current row has the maximum value as its row_end, 2038-01-19 03:14:07.999999. You can also declare the period columns yourself, in which case they remain visible:

CREATE TABLE stock (
    article INT PRIMARY KEY,
    qte     INT NOT NULL,
    debut   TIMESTAMP(6) GENERATED ALWAYS AS ROW START,
    fin     TIMESTAMP(6) GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (debut, fin)
) WITH SYSTEM VERSIONING;

Period columns are of type TIMESTAMP or DATETIME (otherwise error 4146, `d` doit être de type TIMESTAMP(6) pour la table versionnée `mauvais`); TIMESTAMP(6) keeps the microsecond (with fewer decimals, the current end is 2038-01-19 03:14:07 followed by as many 9s). Miraj overwrites any value that an INSERT or an UPDATE gives them (see system_versioning_insert_history, 12.10).

12.2 What writes preserve#

The history follows the transaction: a ROLLBACK undoes it, and after a crash only committed transactions are kept. Ordinary reads (SELECT * FROM stock) see only the current rows; keys (PRIMARY KEY, UNIQUE) apply to them alone.

A versioned table combines with the engine's other objects. A column that takes its value from a sequence (id BIGINT DEFAULT NEXTVAL(ids), see chapter 7) works as on an ordinary table, including after ALTER TABLE … ADD SYSTEM VERSIONING: the history keeps the identifier as it was written, and archive copies do not consume the sequence. A disk-based table (ENGINE = Aria) can be versioned (see 12.13).

12.3 Reading the past: FOR SYSTEM_TIME#

SELECT * FROM stock FOR SYSTEM_TIME AS OF '2025-12-31 23:59:59';
SELECT * FROM stock FOR SYSTEM_TIME AS OF TIMESTAMP '2025-12-31 23:59:59';
SELECT * FROM stock FOR SYSTEM_TIME FROM '2025-01-01' TO '2026-01-01';
SELECT * FROM stock FOR SYSTEM_TIME BETWEEN '2025-01-01' AND '2025-12-31';
SELECT * FROM stock FOR SYSTEM_TIME ALL;

Example: the stock table of 12.1 after an update and a delete.

SET @avant = NOW(6);
UPDATE stock SET qte = 35 WHERE article = 12;
DELETE FROM stock WHERE article = 13;

SELECT * FROM stock;                                  -- current rows
SELECT * FROM stock FOR SYSTEM_TIME AS OF @avant;     -- state before the two writes
SELECT article, qte, row_start, row_end FROM stock FOR SYSTEM_TIME ALL ORDER BY article, row_start;

+---------+-----+
| article | qte |
+---------+-----+
|      12 |  35 |
+---------+-----+

+---------+-----+
| article | qte |
+---------+-----+
|      12 |  40 |
|      13 |   8 |
+---------+-----+

+---------+-----+----------------------------+----------------------------+
| article | qte | row_start                  | row_end                    |
+---------+-----+----------------------------+----------------------------+
|      12 |  40 | 2026-10-19 03:33:14.747306 | 2026-10-19 03:33:15.756576 |
|      12 |  35 | 2026-10-19 03:33:15.756576 | 2038-01-19 03:14:07.999999 |
|      13 |   8 | 2026-10-19 03:33:14.747306 | 2026-10-19 03:33:15.757618 |
+---------+-----+----------------------------+----------------------------+

The clause goes after the table name, before the alias, and applies to each table of a join, a subquery, a derived table, a UNION, the source of an INSERT … SELECT, a view (the clause stays in its definition and applies on every read), and inside a trigger or procedure body:

SELECT c.nom, s.qte
FROM clients c JOIN stock FOR SYSTEM_TIME AS OF '2025-12-31' s ON s.article = c.article;

CREATE VIEW stock_fin_2025 AS SELECT * FROM stock FOR SYSTEM_TIME AS OF '2025-12-31 23:59:59';

The bounds are expressions (NOW(6) - INTERVAL 1 DAY, @instant, ?). Comparing a version to NOW() (to the second) rather than NOW(6) can miss a version written in the current second. A table that is not versioned gives error 4124 (La table `ordinaire` n'est pas versionnée par le système).

12.4 Deleting from the history: DELETE HISTORY#

DELETE HISTORY FROM stock;                                   -- the whole history
DELETE HISTORY FROM stock BEFORE SYSTEM_TIME '2024-01-01';   -- versions ended before this date

Current rows are never touched. The statement returns the number of versions deleted (OK, 1 row(s) affected for a single past version). The bound is an expression (BEFORE SYSTEM_TIME NOW(6) - INTERVAL 1 YEAR).

12.5 Adding or removing versioning, altering a versioned table#

ALTER TABLE stock ADD SYSTEM VERSIONING;
ALTER TABLE stock DROP SYSTEM VERSIONING;

On adding, existing rows start at the time of the ALTER. On removal, the history is deleted and the implicit period columns disappear.

An ALTER TABLE that changes the columns of a versioned table (column added, dropped, modified, renamed, engine, character set) is refused (4119) while system_versioning_alter_history is ERROR (default):

ERROR 4119 (HY000): Interdit sur la table versionnée `tempo`.`stock`. Changez @@system_versioning_alter_history pour poursuivre l'ALTER.

SET system_versioning_alter_history = KEEP;
ALTER TABLE stock ADD COLUMN emplacement VARCHAR(20);

Under KEEP, the change also applies to the history; an added column takes its default value there. Indexes, keys, constraints, options and DROP SYSTEM VERSIONING remain allowed under ERROR. Period columns cannot be modified (1235). RENAME TABLE, DROP TABLE, CREATE TABLE … LIKE and CREATE OR REPLACE TABLE follow the table.

12.6 Application-time periods: PERIOD FOR#

CREATE TABLE tarif (
    article INT,
    prix    DECIMAL(10,2),
    debut   DATE,
    fin     DATE,
    PERIOD FOR validite (debut, fin)
);
ALTER TABLE contrat ADD PERIOD IF NOT EXISTS FOR duree (signe_le, echeance);
ALTER TABLE contrat DROP PERIOD IF EXISTS FOR duree;

The two columns have the same type, DATE, DATETIME or TIMESTAMP (otherwise 1063 or 4153), are distinct (1110), neither generated nor system-versioning columns (4155); they become NOT NULL. Only one application period per table (4154), never on a temporary table (4152). Every row must satisfy start < end: otherwise error 4025 CONSTRAINTvaliditefailed forbase.tarif`, on write as well as when the period is added over existing rows. This constraint can only be removed byDROP PERIOD(DROP CONSTRAINT validite: 4158). The period is shown bySHOW CREATE TABLE(PERIOD FOR validite (debut, fin)), follows the renaming of its columns and appears ininformation_schema.PERIODS(along with theSYSTEM_TIME` period of versioned tables).

The period is closed at the start and open at the end: [debut, fin).

INSERT INTO tarif VALUES (13, 5, '2026-05-01', '2026-04-01');
ERROR 4025 (23000): CONSTRAINT `validite` failed for `tempo`.`tarif`

12.7 Changing a portion of a period: FOR PORTION OF#

UPDATE tarif FOR PORTION OF validite FROM '2026-03-01' TO '2026-04-01'
   SET prix = prix * 0.9 WHERE article = 12;
DELETE FROM tarif FOR PORTION OF validite FROM '2026-07-01' TO '2026-09-01' WHERE article = 12;

With a single row (12, 20.00, 2026-01-01, 2027-01-01) to begin with, the two statements above leave:

SELECT * FROM tarif ORDER BY debut;

+---------+-------+------------+------------+
| article | prix  | debut      | fin        |
+---------+-------+------------+------------+
|      12 | 20.00 | 2026-01-01 | 2026-03-01 |
|      12 | 18.00 | 2026-03-01 | 2026-04-01 |
|      12 | 20.00 | 2026-04-01 | 2026-07-01 |
|      12 | 20.00 | 2026-09-01 | 2027-01-01 |
+---------+-------+------------+------------+

Only the rows whose period overlaps [a, b) are targeted. A row that extends beyond the interval is split: the portion [max(start, a), min(end, b)) is modified (or deleted), and the rest of its period is kept in one or two inserted rows, copies of the original row before modification, with periods [start, a) and [b, end). Example: the row ('b', 1999-01-01, 2018-12-12) after DELETE … FOR PORTION OF p FROM '2001-01-01' TO '2018-01-01' becomes ('b', 1999-01-01, 2001-01-01) and ('b', 2018-01-01, 2018-12-12).

The table's INSERT triggers run for each inserted piece, in addition to the UPDATE or DELETE triggers of the portion. The table alias is written after the clause (UPDATE t FOR PORTION OF p FROM a TO b AS x SET …). WHERE, ORDER BY and LIMIT apply; an empty interval touches nothing. A period column in SET: 4157; unknown period: 4156. The statement is atomic: a piece that violates a key cancels everything. The number of affected rows is that of the portions modified or deleted.

12.8 Forbidding overlaps: WITHOUT OVERLAPS#

CREATE TABLE affectation (
    employe INT,
    poste   VARCHAR(20),
    debut   DATE,
    fin     DATE,
    PERIOD FOR p (debut, fin),
    PRIMARY KEY (employe, p WITHOUT OVERLAPS)
);
ALTER TABLE tarif ADD UNIQUE u_tarif (article, validite WITHOUT OVERLAPS);

INSERT INTO affectation VALUES (1, 'caisse',  '2026-01-01', '2026-02-01');
INSERT INTO affectation VALUES (1, 'accueil', '2026-02-01', '2026-03-01');  -- contiguous: allowed
INSERT INTO affectation VALUES (1, 'stock',   '2026-01-20', '2026-02-10');

ERROR 1062 (23000): Duplicate entry '1-2026-02-10-2026-01-20' for key 'PRIMARY'

Two rows with the same key (without NULL) cannot have overlapping periods: error 1062 (the message cites the key columns, then the end and start of the refused row), on insert, on update, under FOR PORTION OF (the pieces never overlap the portion) and when the key is added over existing rows. Contiguous periods ([1, 5) and [5, 9)) do not overlap. Under concurrency, two transactions that write overlapping rows cannot both commit: the second fails at commit (1213, to be replayed). The key cannot cite a column of the period (4170); a non-unique index does not accept WITHOUT OVERLAPS (4171). The keys appear in information_schema.KEY_PERIOD_USAGE. A period used by such a key cannot be removed (1235): remove the key first.

12.9 History partitions: PARTITION BY SYSTEM_TIME#

CREATE TABLE mouvement (id INT, qte INT) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME INTERVAL 1 MONTH STARTS '2026-01-01 00:00:00'
(PARTITION h_janvier HISTORY, PARTITION h_fevrier HISTORY, PARTITION courant CURRENT);

CREATE TABLE journal (id INT) WITH SYSTEM VERSIONING
PARTITION BY SYSTEM_TIME LIMIT 100000 PARTITIONS 4;      -- p0, p1, p2 HISTORY then pn CURRENT

The partitions are logical: past versions stay in the companion table (12.11), and a version belongs to a HISTORY partition according to its period end (INTERVAL n unit [STARTS …], the first also receiving earlier versions, the last those after) or its rank in the order of ends (LIMIT n, the last receiving the rest); with no criterion, the whole history is in the last one. There must be at least one HISTORY partition and exactly one CURRENT, the last (4128); zero INTERVAL or LIMIT: 4127; no STARTS: start of the creation day. AUTO is kept and displayed again, without creating a partition.

• SELECT … FROM mouvement PARTITION (h_janvier, courant) reads these partitions (HISTORY partition of a LIMIT rotation: 1235); other reads do not change.
• ALTER TABLE mouvement DROP PARTITION h_janvier deletes the versions of the partition and removes it (the oldest only under INTERVAL or LIMIT; STARTS advances accordingly); the CURRENT partition or the last HISTORY one cannot be removed (4128).
• ALTER TABLE mouvement TRUNCATE PARTITION h_fevrier deletes its versions and keeps it.
• ALTER TABLE mouvement REMOVE PARTITIONING removes the partitions (the history stays); DROP SYSTEM VERSIONING on a table partitioned this way: 4131. PARTITION BY SYSTEM_TIME can only be written at creation (in an ALTER TABLE: 1235).

SHOW CREATE TABLE, CREATE TABLE … LIKE and information_schema.PARTITIONS (SYSTEM_TIME method, HISTORY / CURRENT description) show the definition.

12.10 Variables#

All three can be set for the session (SET) or for following sessions (SET GLOBAL). The value of system_versioning_asof is evaluated at the SET (SET system_versioning_asof = NOW() - INTERVAL 1 DAY) and is displayed to the microsecond.

12.11 Under the hood#

The history lives in a companion table __sv_<table> in the same database, with no keys or constraints, which SHOW TABLES and information_schema hide. It can be read like an ordinary table (SELECT * FROM __sv_stock), with the privileges of its table. information_schema.TABLES.TABLE_TYPE is SYSTEM VERSIONED. BACKUP / RESTORE copy it along with the table; miraj-dump exports only the current rows (the script recreates a versioned table, without history), with its application periods, WITHOUT OVERLAPS keys and SYSTEM_TIME partitions. A table name that starts with __sv_ is reserved.

The start < end constraint of an application period and the refusal of overlaps are implemented by synthetic triggers, like the writing of the history: they appear neither in SHOW TRIGGERS nor in information_schema.TRIGGERS. A WITHOUT OVERLAPS key is stored as a unique index on (columns, end, start).

12.12 Error codes#

12.13 Differences from the reference engine#

• A single transaction that modifies a row twice leaves two versions of it (the reference leaves only one).
• FOR PORTION OF inserts the remaining pieces after all the modified rows (the reference: row by row); the execution order of INSERT triggers therefore differs when several rows are split.
• ALTER TABLE … ADD of a WITHOUT OVERLAPS key compares text key values without case or fine collation; on write, the comparison is that of the column.
• PARTITION BY SYSTEM_TIME: logical partitions (a single companion table), AUTO without automatic creation, ADD PARTITION and ALTER TABLE … PARTITION BY SYSTEM_TIME not supported (1235).
• system_versioning_insert_history does not allow inserting an already ended version (1235).
• Not supported: versioning by transaction ID (BIGINT UNSIGNED columns: 4146, AS OF TRANSACTION: 1235), per-column WITH / WITHOUT SYSTEM VERSIONING, versioned temporary tables, versioning of a table partitioned by RANGE / LIST / HASH.
• Period columns are TIMESTAMPs: they follow the session time_zone like any TIMESTAMP column, and FOR SYSTEM_TIME compares instants, not displayed times. The period end of a current row, written as 2038-01-19 03:14:07.999999 in the time zone of the writing session, therefore displays shifted under another time zone.
• A disk-based table (ENGINE = Aria…) can be versioned; its companion table remains an in-memory table.
• Deletions and modifications made by a foreign key action (ON DELETE CASCADE…) leave no history: they fire nothing, as with triggers.