mariadb_temporal.sql

CREATE TABLE Employees (
     id                      INT NOT NULL AUTO_INCREMENT
   , emp_id                  INTEGER
   , first_name              VARCHAR(100)
   , last_name               VARCHAR(100)
   , state                   VARCHAR(50)
   , department              VARCHAR(20)

   , start_date               DATETIME NOT NULL DEFAULT "1970-00-00 00:00:00"
   , end_date                 DATETIME NOT NULL DEFAULT "2039-01-01 00:00:00"

   , created_at               TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP 
   , updated_at               TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP 

   , row_start TIMESTAMP(6) AS ROW START INVISIBLE
   , row_end TIMESTAMP(6) AS ROW END INVISIBLE

   , PERIOD FOR valid_time (start_date, end_date)
   , PERIOD FOR system_time (row_start, row_end)

   , PRIMARY KEY (`id`)
   , UNIQUE (emp_id, valid_time WITHOUT OVERLAPS)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 WITH SYSTEM VERSIONING;



INSERT INTO Employees (emp_id, first_name, last_name, state, department)
     VALUES (1, "Anil", "Vaitla", "Illinois", "Human Resources");


SELECT * FROM Employees;

mysql> SELECT * FROM Employees;
+----+--------+------------+-----------+----------+-----------------+---------------------+---------------------+
| id | emp_id | first_name | last_name | state    | department      | start_date          | end_date            |
+----+--------+------------+-----------+----------+-----------------+---------------------+---------------------+
|  1 |      1 | Anil       | Vaitla    | Illinois | Human Resources | 1970-00-00 00:00:00 | 2039-01-01 00:00:00 |
+----+--------+------------+-----------+----------+-----------------+---------------------+---------------------+
1 row in set (0.04 sec)



UPDATE Employees SET state = "California" WHERE emp_id = 1;

mysql> SELECT * FROM Employees;
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+
| id | emp_id | first_name | last_name | state      | department      | start_date          | end_date            |
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+
|  1 |      1 | Anil       | Vaitla    | California | Human Resources | 1970-00-00 00:00:00 | 2039-01-01 00:00:00 |
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+
1 row in set (0.05 sec)



mysql> SELECT *, row_start, row_end FROM Employees FOR SYSTEM_TIME ALL;
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+----------------------------+----------------------------+
| id | emp_id | first_name | last_name | state      | department      | start_date          | end_date            | row_start                  | row_end                    |
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+----------------------------+----------------------------+
|  1 |      1 | Anil       | Vaitla    | Illinois   | Human Resources | 1970-00-00 00:00:00 | 2039-01-01 00:00:00 | 2022-01-21 15:45:36.543425 | 2022-01-21 15:45:53.020638 |
|  1 |      1 | Anil       | Vaitla    | California | Human Resources | 1970-00-00 00:00:00 | 2039-01-01 00:00:00 | 2022-01-21 15:45:53.020638 | 2038-01-19 03:14:07.999999 |
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+----------------------------+----------------------------+
2 rows in set (0.04 sec)



UPDATE Employees FOR PORTION OF valid_time 
  FROM '2000-01-01 00:00:00' TO '2001-01-01 00:00:00' 
   SET department="Sales" WHERE emp_id=1;

mysql> SELECT * FROM Employees;
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+
| id | emp_id | first_name | last_name | state      | department      | start_date          | end_date            |
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+
|  1 |      1 | Anil       | Vaitla    | California | Sales           | 2000-01-01 00:00:00 | 2001-01-01 00:00:00 |
|  2 |      1 | Anil       | Vaitla    | California | Human Resources | 1970-00-00 00:00:00 | 2000-01-01 00:00:00 |
|  3 |      1 | Anil       | Vaitla    | California | Human Resources | 2001-01-01 00:00:00 | 2039-01-01 00:00:00 |
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+
3 rows in set (0.03 sec)


-- Protects overlaps
mysql> INSERT INTO Employees (emp_id, first_name, last_name, state, department)   
       VALUES (1, "Anil", "Vaitla", "Illinois", "Human Resources");
ERROR 1062 (23000): Duplicate entry '1-2038-01-19 03:14:07.999999-2039-01-01 00:00:00-1970-00-00 0...' for key 'emp_id'


DELETE FROM Employees FOR PORTION OF valid_time 
  FROM '2000-06-01 00:00:00' TO '2005-01-01 00:00:00' 
  WHERE emp_id=1;


mysql> SELECT * FROM Employees;
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+
| id | emp_id | first_name | last_name | state      | department      | start_date          | end_date            |
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+
|  2 |      1 | Anil       | Vaitla    | California | Human Resources | 1970-00-00 00:00:00 | 2000-01-01 00:00:00 |
|  5 |      1 | Anil       | Vaitla    | California | Sales           | 2000-01-01 00:00:00 | 2000-06-01 00:00:00 |
|  6 |      1 | Anil       | Vaitla    | California | Human Resources | 2005-01-01 00:00:00 | 2039-01-01 00:00:00 |
+----+--------+------------+-----------+------------+-----------------+---------------------+---------------------+
3 rows in set (0.03 sec)


mysql> SELECT * FROM Employees FOR SYSTEM_TIME AS OF TIMESTAMP '2022-01-21 15:45:37';
+----+--------+------------+-----------+----------+-----------------+---------------------+---------------------+
| id | emp_id | first_name | last_name | state    | department      | start_date          | end_date            |
+----+--------+------------+-----------+----------+-----------------+---------------------+---------------------+
|  1 |      1 | Anil       | Vaitla    | Illinois | Human Resources | 1970-00-00 00:00:00 | 2039-01-01 00:00:00 |
+----+--------+------------+-----------+----------+-----------------+---------------------+---------------------+
1 row in set (0.03 sec)