Description\n-----------\n\nExtending system-versioned tables, MariaDB 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.\n\nUsing 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.\n\nCreating Tables with Time Periods\n\nTo create a table with a time period, use a CREATE TABLE statement with the PERIOD table option.\n\n``sql\nCREATE TABLE t1(\n   name VARCHAR(50), \n   date_1 DATE,\n   date_2 DATE,\n   PERIOD FOR date_period(date_1, date_2));\n`\n\nThis creates a table with a time_period period and populates the table with some basic temporal values.\n\nExamples are available in the MariaDB Server source code, at mysql-test/suite/period/r/create.result.\n\nAdding and Removing Time Periods\n\nThe ALTER TABLE statement now supports syntax for adding and removing time periods from a table. To add a period, use the ADD PERIOD clause:\n\n`sql\nCREATE OR REPLACE TABLE rooms (\n room_number INT,\n guest_name VARCHAR(255),\n checkin DATE,\n checkout DATE\n );\n\nALTER TABLE rooms ADD PERIOD FOR p(checkin,checkout);\n`\n\nTo remove a period, use the DROP PERIOD clause:\n\n`sql\nALTER TABLE rooms DROP PERIOD FOR p;\n`\n\nBoth ADD PERIOD and DROP PERIOD clauses include an option to handle whether the period already exists:\n\n`sql\nALTER TABLE rooms ADD PERIOD IF NOT EXISTS FOR p(checkin,checkout);\n\nALTER TABLE rooms DROP PERIOD IF EXISTS FOR p;\n`\n\nDeletion by Portion\n\nYou can also remove rows that fall within certain time periods.\n\nWhen MariaDB executes a DELETE FOR PORTION statement, it removes the row:\n\n When the row period falls completely within the delete period, it removes the row.\n When the row period overlaps the delete period, it shrinks the row, removing the overlap from the first or second row period value.\n 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.\n\nTo test this, first populate the table with some data to operate on:\n\n`sql\nCREATE TABLE t1(\n   name VARCHAR(50), \n   date_1 DATE,\n   date_2 DATE,\n   PERIOD FOR date_period(date_1, date_2));\n\nINSERT INTO t1 (name, date_1, date_2) VALUES\n    ('a', '1999-01-01', '2000-01-01'),\n    ('b', '1999-01-01', '2018-12-12'),\n    ('c', '1999-01-01', '2017-01-01'),\n    ('d', '2017-01-01', '2019-01-01');\n\nSELECT  FROM t1;\n+------+------------+------------+\n| name | date_1     | date_2     |\n+------+------------+------------+\n| a    | 1999-01-01 | 2000-01-01 |\n| b    | 1999-01-01 | 2018-12-12 |\n| c    | 1999-01-01 | 2017-01-01 |\n| d    | 2017-01-01 | 2019-01-01 |\n+------+------------+------------+\n`\n\nThen, run the DELETE FOR PORTION statement:\n\n`sql\nDELETE FROM t1\nFOR PORTION OF date_period\n    FROM '2001-01-01' TO '2018-01-01';\nQuery OK, 3 rows affected (0.028 sec)\n\nSELECT  FROM t1 ORDER BY name;\n+------+------------+------------+\n| name | date_1     | date_2     |\n+------+------------+------------+\n| a    | 1999-01-01 | 2000-01-01 |\n| b    | 1999-01-01 | 2001-01-01 |\n| b    | 2018-01-01 | 2018-12-12 |\n| c    | 1999-01-01 | 2001-01-01 |\n| d    | 2018-01-01 | 2019-01-01 |\n+------+------------+------------+\n`\n\nHere:\n\n a is unchanged, as the range falls entirely out of the specified portion to be deleted.\n b, with values ranging from 1999 to 2018, is split into two rows, 1999 to 2000 and 2018-01 to 2018-12 (i.e. one extra row has been inserted).\n 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.\n 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.\n\nThe DELETE FOR PORTION statement has the following restrictions\n\n The FROM...TO clause must be constant.\n Multi-delete is not supported.\n\nIf there are DELETE or INSERT triggers, any matched row is deleted, and then one or two rows are inserted. If the record is deleted completely, nothing is inserted.\n\nUpdating by Portion\n\nThe UPDATE syntax now supports UPDATE FOR PORTION, which modifies rows based on their occurrence in a range:\n\nTo test it, first populate the table with some data:\n\n`sql\nTRUNCATE t1;\n\nINSERT INTO t1 (name, date_1, date_2) VALUES\n    ('a', '1999-01-01', '2000-01-01'),\n    ('b', '1999-01-01', '2018-12-12'),\n    ('c', '1999-01-01', '2017-01-01'),\n    ('d', '2017-01-01', '2019-01-01');\n\nSELECT  FROM t1;\n+------+------------+------------+\n| name | date_1     | date_2     |\n+------+------------+------------+\n| a    | 1999-01-01 | 2000-01-01 |\n| b    | 1999-01-01 | 2018-12-12 |\n| c    | 1999-01-01 | 2017-01-01 |\n| d    | 2017-01-01 | 2019-01-01 |\n+------+------------+------------+\n`\n\nThen run the update:\n\n`sql\nUPDATE t1 FOR PORTION OF date_period\n  FROM '2000-01-01' TO '2018-01-01' \nSET name = CONCAT(name,'_original');\n\nSELECT  FROM t1 ORDER BY name;\n+------------+------------+------------+\n| name       | date_1     | date_2     |\n+------------+------------+------------+\n| a          | 1999-01-01 | 2000-01-01 |\n| b          | 1999-01-01 | 2000-01-01 |\n| b          | 2018-01-01 | 2018-12-12 |\n| b_original | 2000-01-01 | 2018-01-01 |\n| c          | 1999-01-01 | 2000-01-01 |\n| c_original | 2000-01-01 | 2017-01-01 |\n| d          | 2018-01-01 | 2019-01-01 |\n| d_original | 2017-01-01 | 2018-01-01 |\n+------------+------------+------------+\n`\n\n a is unchanged, as the range falls entirely out of the specified portion to be updated.\n For b, with years ranging from 1999 to 2018, two extra rows are inserted, with ranges 1999-01 to 2000-01 and 2018-01 to 2018-12. The original row's period has been shrunk to years 2000 and 2018, and the name field has got "_original" appended.\n c, with values ranging from 1999 to 2017, where only the upper value falls within the portion to be updated, has been shrunk to 1999 to 2001.\n d, with values ranging from 2017 to 2019, where only the lower value falls within the portion to be updated, has been shrunk to 2018 to 2019.\n Original rows affected by the update have "_original" appended to the name.\n\nThe UPDATE FOR PORTION statement has the following limitations:\n\n The operation cannot modify the two temporal columns used by the time period.\n The operation cannot reference period values in the SET expression.\n FROM...TO expressions must be constant.\n\nWITHOUT OVERLAPS\n\nThis clause is available from MariaDB 10.5.3.\n\nWITHOUT OVERLAPS allows 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.\n\nTake the following example, an application time period table for a booking system:\n\n`sql\nCREATE OR REPLACE TABLE rooms (\n room_number INT,\n guest_name VARCHAR(255),\n checkin DATE,\n checkout DATE,\n PERIOD FOR p(checkin,checkout)\n );\n\nINSERT INTO rooms VALUES \n (1, 'Regina', '2020-10-01', '2020-10-03'),\n (2, 'Cochise', '2020-10-02', '2020-10-05'),\n (1, 'Nowell', '2020-10-03', '2020-10-07'),\n (2, 'Eusebius', '2020-10-04', '2020-10-06');\n`\n\nOur 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 to specify this constraint in the table definition.\n\n`sql\nCREATE OR REPLACE TABLE rooms (\n room_number INT,\n guest_name VARCHAR(255),\n checkin DATE,\n checkout DATE,\n PERIOD FOR p(checkin,checkout),\n UNIQUE (room_number, p WITHOUT OVERLAPS)\n );\n\nINSERT INTO rooms VALUES \n (1, 'Regina', '2020-10-01', '2020-10-03'),\n (2, 'Cochise', '2020-10-02', '2020-10-05'),\n (1, 'Nowell', '2020-10-03', '2020-10-07'),\n (2, 'Eusebius', '2020-10-04', '2020-10-06');\nERROR 1062 (23000): Duplicate entry '2-2020-10-06-2020-10-04' for key 'room_number'\n`\n\nInformation Schema\n\nInformation Schema support for application time period tables is available from MariaDB 11.4.\n\nInformation Schema contains the following support for application time period tables:\n\n INFORMATION_SCHEMA.PERIODS view.\n INFORMATION_SCHEMA.KEY_PERIOD_USAGE view.\n Additional columns IS_SYSTEM_TIME_PERIOD_START and IS_SYSTEM_TIME_PERIOD_END in the INFORMATION_SCHEMA.COLUMNS view.\n\nFurther Examples\n\nThe implicit change from NULL to NOT NULL:\n\n`sql\nCREATE TABLE t2 (\n  id int(11) DEFAULT NULL,\n  d1 datetime DEFAULT NULL,\n  d2 datetime DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\nALTER TABLE t2 ADD PERIOD FOR p(d1,d2);\n\nSHOW CREATE TABLE t2\\G\n************************ 1. row ***********************\n       Table: t2\nCreate Table: CREATE TABLE t2 (\n //a// is unchanged, as the range falls entirely out of the specified portion to be updated.\n For //b//, with years ranging from 1999 to 2018, two extra rows are inserted, with ranges 1999-01 to 2000-01 and 2018-01 to 2018-12. The original row's period has been shrunk to years 2000 and 2018, and the _name_ field has got "_original" appended.\n //c//, with values ranging from 1999 to 2017, where only the upper value falls within the portion to be updated, has been shrunk to 1999 to 2001.\n //d//, with values ranging from 2017 to 2019, where only the lower value falls within the portion to be updated, has been shrunk to 2018 to 2019. \n Original rows affected by the update have "_original" appended to the ##name## field.\n  id int(11) DEFAULT NULL,\n  d1 datetime NOT NULL,\n  d2 datetime NOT NULL,\n  PERIOD FOR p (d1, d2)\n) ENGINE=InnoDB DEFAULT CHARSET=latin1\n`\n\nDue to this constraint, trying to add a time period where NULL data already exist fails:\n\n`sql\nCREATE OR REPLACE TABLE t2 (\n  id int(11) DEFAULT NULL,\n  d1 datetime DEFAULT NULL,\n  d2 datetime DEFAULT NULL\n) ENGINE=InnoDB DEFAULT CHARSET=latin1;\n\nINSERT INTO t2(id) VALUES(1);\n\nALTER TABLE t2 ADD PERIOD FOR p(d1,d2);\nERROR 1265 (01000): Data truncated for column 'd1' at row 1\n``\n\nURL: https://mariadb.com/docs/server/reference/sql-structure/temporal-tables/application-time-periods