
 FROM '2000-01-01' TO '2018-01-01'
SET name = CONCAT(name,'_original');

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

* 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. 
* Original rows affected by the update have "_original" appended to the name.

The UPDATE FOR PORTION statement has the following limitations:

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

WITHOUT OVERLAPS
----------------

MariaDB starting with 10.5.3
----------------------------
MariaDB 10.5 introduced a new clause, WITHOUT OVERLAPS, which allows one to
create an index specifying that application time periods should not overlap.

An index constrained by WITHOUT OVERLAPS is required to be either a primary
key or a unique index.

Take the following example, an application time period table for a booking
system:

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

INSERT INTO rooms VALUES 
 (1, 'Regina', '2020-10-01', '2020-10-03'),
 (2, 'Cochise', '2020-10-02', '2020-10-05'),
 (1, 'Nowell', '2020-10-03', '2020-10-07'),
 (2, 'Eusebius', '2020-10-04', '2020-10-06');

Our system is not intended to permit overlapping bookings, so the fourth
record above should not have been inserted. Using WITHOUT OVERLAPS in a unique
index (in this case based on a combination of room number and the application
time period) allows us to specify this constraint in the table definition.

CREATE OR REPLACE TABLE rooms (
 room_number INT,
 guest_name VARCHAR(255),
 checkin DATE,
 checkout DATE,
 PERIOD FOR p(checkin,checkout),
 UNIQUE (room_number, p WITHOUT OVERLAPS)
 );

INSERT INTO rooms VALUES 
 (1, 'Regina', '2020-10-01', '2020-10-03'),
 (2, 'Cochise', '2020-10-02', '2020-10-05'),
 (1, 'Nowell', '2020-10-03', '2020-10-07'),
 (2, 'Eusebius', '2020-10-04', '2020-10-06');
ERROR 1062 (23000): Duplicate entry '2-2020-10-06-2020-10-04' for key
'room_number'

Further Examples
----------------

The implicit change from NULL to NOT NULL:

CREATE TABLE `t2` (
 `id` int(11) DEFAULT NULL,
 `d1` datetime DEFAULT NULL,
 `d2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE t2 ADD PERIOD FOR p(d1,d2);

SHOW CREATE TABLE t2\G
*************************** 1. row ***************************
   Table: t2
Create Table: CREATE TABLE `t2` (
 `id` int(11) DEFAULT NULL,
 `d1` datetime NOT NULL,
 `d2` datetime NOT NULL,
 PERIOD FOR `p` (`d1`, `d2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Due to this constraint, trying to add a time period where null data already
exists will fail.

CREATE OR REPLACE TABLE `t2` (
 `id` int(11) DEFAULT NULL,
 `d1` datetime DEFAULT NULL,
 `d2` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO t2(id) VALUES(1);

ALTER TABLE t2 ADD PERIOD FOR p(d1,d2);
ERROR 1265 (01000): Data truncated for column 'd1' at row 1

URL: https://mariadb.com/kb/en/application-time-periods/