Application-Time Periods

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, such as DATE or TIMESTAMP, (though not TIME, which is currently unsupported), 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 test.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.

CREATE TABLE test.t2 (
   id INT PRIMARY KEY,
   time_1 TIMESTAMP(6),
   time_2 TIMESTAMP(6)
);


ALTER TABLE test.t2 ADD PERIOD FOR time_period(time_1, time_2);

To remove a period, use the DROP PERIOD clause:

ALTER TABLE test.t2 DROP PERIOD FOR time_period;

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

ALTER TABLE test.t2 ADD PERIOD IF NOT EXISTS FOR time_period(time_1, time_2);

ALTER TABLE test.t2 DROP PERIOD IF EXISTS FOR time_period;

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:

TRUNCATE test.t1;

INSERT INTO test.t1 (date_1, date_2)
VALUES ('1999-01-01', '2018-12-12'),
       ('1999-01-01', '2017-01-01'),
       ('2017-01-01', '2019-01-01'),
       ('1998-01-01', '2018-12-12'),
       ('1997-01-01', '2015-01-01'),
       ('2016-01-01', '2020-01-01'),
       ('2010-01-01', '2015-01-01');


SELECT * FROM test.t1;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| NULL | 1999-01-01 | 2018-12-12 |
| NULL | 1999-01-01 | 2017-01-01 |
| NULL | 2017-01-01 | 2019-01-01 |
| NULL | 1998-01-01 | 2018-12-12 |
| NULL | 1997-01-01 | 2015-01-01 |
| NULL | 2016-01-01 | 2020-01-01 |
| NULL | 2010-01-01 | 2015-01-01 |
+----+------+------------+------------+

Then, run the DELETE FOR PORTION statement:

DELETE FROM test.t1
FOR PORTION OF date_period
    FROM '2001-01-01' TO '2018-01-01';


SELECT * FROM test.t1;
+------+------------+------------+
| name | date_1     | date_2     |
+------+------------+------------+
| NULL | 1999-01-01 | 2001-01-01 |
| NULL | 1999-01-01 | 2001-01-01 |
| NULL | 2018-01-01 | 2019-01-01 |
| NULL | 1998-01-01 | 2001-01-01 |
| NULL | 1997-01-01 | 2001-01-01 |
| NULL | 2018-01-01 | 2020-01-01 |
| NULL | 2018-01-01 | 2018-12-12 |
| NULL | 2018-01-01 | 2018-12-12 |
+------+------------+------------+

Here, second row, with values ranging from 1999 to 2017 has been shrunk to 1999 to 2001. The first row, with a range from 1999 to 2018 split into two rows 1999 to 2000 and 2018-01 to 2018-12. The fifth row, ranging from 2010 to 2015 was deleted entirely.

The DELETE FOR PORTION statement has the following restrictions

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

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 test.t1;

INSERT INTO test.t1 (date_1, date_2)
VALUES ('1999-01-01', '2018-12-12'),
       ('1999-01-01', '2017-01-01'),
       ('2017-01-01', '2019-01-01'),
       ('1998-01-01', '2018-12-12'),
       ('1997-01-01', '2015-01-01'),
       ('2016-01-01', '2020-01-01'),
       ('2010-01-01', '2015-01-01');

Then run the update:

UPDATE test.t1 
FOR PORTION OF date_period
    FROM '2000-01-01' TO '2018-01-01'
SET name = "Generation Z";

SELECT * FROM test.t1;
+--------------+------------+------------+
 name         | date_1     | date_2     |
+--------------+------------+------------+
| Generation Z | 2000-01-01 | 2018-01-01 |
| Generation Z | 2000-01-01 | 2017-01-01 |
| Generation Z | 2017-01-01 | 2018-01-01 |
| Generation Z | 2000-01-01 | 2018-01-01 |
| Generation Z | 2000-01-01 | 2015-01-01 |
| Generation Z | 2016-01-01 | 2018-01-01 |
| Generation Z | 2010-01-01 | 2015-01-01 |
| NULL         | 1999-01-01 | 2000-01-01 |
| NULL         | 2018-01-01 | 2018-12-12 |
| NULL         | 1999-01-01 | 2000-01-01 |
| NULL         | 2018-01-01 | 2019-01-01 |
| NULL         | 1998-01-01 | 2000-01-01 |
| NULL         | 2018-01-01 | 2018-12-12 |
| NULL         | 1997-01-01 | 2000-01-01 |
| NULL         | 2018-01-01 | 2020-01-01 |
+--------------+------------+------------+

The UPDATE FOR PORTION statement has the following limitations:

• Operation cannot modify the two temporal columns used by the time period
• Operation cannot reference period values in the SET expression
• FROM...TO expressions must be constant

See Also

• System-versioned Tables
• Bitemporal Tables