MariaDB starting with 10.4.3
----------------------------
Support for application-time period-versioning was added in MariaDB 10.4.3.

Extending system-versioned tables, MariaDB 10.4 supports application-time
period tables. Time periods are defined by a range between two temporal
columns. The columns must be of the same temporal data type, i.e. DATE,
TIMESTAMP or DATETIME (TIME and YEAR are not supported), and of the same width.

Using time periods implicitly defines the two columns as NOT NULL. It also
adds a constraint to check whether the first value is less than the second
value. The constraint is invisible to SHOW CREATE TABLE statements. The name
of this constraint is prefixed by the time period name, to avoid conflict with
other constraints.

Creating Tables with Time Periods
---------------------------------

To create a table with a time period, use a CREATE TABLE statement with the
PERIOD table option.

CREATE TABLE t1(
 name VARCHAR(50),
 date_1 DATE,
 date_2 DATE,
 PERIOD FOR date_period(date_1, date_2));

This creates a table with a time_period period and populates the table with
some basic temporal values.

Examples are available in the MariaDB Server source code, at
mysql-test/suite/period/r/create.result.

Adding and Removing Time Periods
--------------------------------

The ALTER TABLE statement now supports syntax for adding and removing time
periods from a table. To add a period, use the ADD PERIOD clause.

For example:

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE
 );

ALTER TABLE rooms ADD PERIOD FOR p(checkin,checkout);

To remove a period, use the DROP PERIOD clause:

ALTER TABLE rooms DROP PERIOD FOR p;

Both ADD PERIOD and DROP PERIOD clauses include an option to handle whether
the period already exists:

ALTER TABLE rooms ADD PERIOD IF NOT EXISTS FOR p(checkin,checkout);

ALTER TABLE rooms DROP PERIOD IF EXISTS FOR p;

Deletion by Portion
-------------------

You can also remove rows that fall within certain time periods.

When MariaDB executes a DELETE FOR PORTION statement, it removes the row:

* When the row period falls completely within the delete period, it removes
the row.
* When the row period overlaps the delete period, it shrinks the row, removing
the overlap from the first or second row period value.
* When the delete period falls completely within the row period, it splits the
row into two rows.  The first row runs from the starting row period to the
starting delete period.  The second runs from the ending delete period to the
ending row period.

To test this, first populate the table with some data to operate on:

CREATE TABLE t1(
 name VARCHAR(50),
 date_1 DATE,
 date_2 DATE,
 PERIOD FOR date_period(date_1, date_2));

INSERT INTO t1 (name, date_1, date_2) VALUES
  ('a', '1999-01-01', '2000-01-01'),
  ('b', '1999-01-01', '2018-12-12'),
  ('c', '1999-01-01', '2017-01-01'),
  ('d', '2017-01-01', '2019-01-01');

SELECT * FROM t1;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2017-01-01 |
| d    | 2017-01-01 | 2019-01-01 |
+------+------------+------------+

Then, run the DELETE FOR PORTION statement:

DELETE FROM t1
FOR PORTION OF date_period
  FROM '2001-01-01' TO '2018-01-01';
Query OK, 3 rows affected (0.028 sec)

SELECT * FROM t1 ORDER BY name;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2001-01-01 |
| b    | 2018-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2001-01-01 |
| d    | 2018-01-01 | 2019-01-01 |
+------+------------+------------+

Here:

* a is unchanged, as the range falls entirely out of the specified portion to
be deleted.
* b, with values ranging from 1999 to 2018, is split into two rows, 1999 to
2000 and 2018-01 to 2018-12.
* c, with values ranging from 1999 to 2017, where only the upper value falls
within the portion to be deleted, has been shrunk to 1999 to 2001.
* d, with values ranging from 2017 to 2019, where only the lower value falls
within the portion to be deleted, has been shrunk to 2018 to 2019.

The DELETE FOR PORTION statement has the following restrictions

* The FROM...TO clause must be constant
* Multi-delete is not supported

If there are DELETE or INSERT triggers, it works as follows: any matched row
is deleted, and then one or two rows are inserted. If the record is deleted
completely, nothing is inserted.

Updating by Portion
-------------------

The UPDATE syntax now supports UPDATE FOR PORTION, which modifies rows based
on their occurrence in a range:

To test it, first populate the table with some data:

TRUNCATE t1;

INSERT INTO t1 (name, date_1, date_2) VALUES
  ('a', '1999-01-01', '2000-01-01'),
  ('b', '1999-01-01', '2018-12-12'),
  ('c', '1999-01-01', '2017-01-01'),
  ('d', '2017-01-01', '2019-01-01');

SELECT * FROM t1;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| a    | 1999-01-01 | 2000-01-01 |
| b    | 1999-01-01 | 2018-12-12 |
| c    | 1999-01-01 | 2017-01-01 |
| d    | 2017-01-01 | 2019-01-01 |
+------+------------+------------+

Then run the update:

UPDATE t1 FOR PORTION OF date_period