Time series functionality within MySQL
Time series data and temporal algebra, in MySQL. Enough said.
More information
During my database course, we had to implement a variety of interesting use cases: time series databases, geospatial databases and deductive databases, as well as others.
What I did
The time series use case seemed simple enough, so I picked MySQL for this - a DBMS that's reasonably easy to run and use, with some capability for stored procedures, triggers and all that nice stuff. As a part of this course, I implemented Allen's interval algebra and demonstrated using it in an example schema.
What I learnt
Admittedly, my implementation was just a collection of stored procedures that one could add to almost any database out there. However I used JSON for operating on some of the internal data structures not to bloat the amount of rows too much, which in hindsight was nice at the time for meeting deadlines, but otherwise wasn't all that good of a solution.
Here's a quick code example for why this isn't perhaps the best solution:
-- During
-- A
-- |===|
-- |=====|
-- B
DROP FUNCTION IF EXISTS `A_During` $$
CREATE FUNCTION `A_During` (A_JSON JSON, B_JSON JSON) RETURNS BOOLEAN
BEGIN
DECLARE A_ID INT;
DECLARE B_ID INT;
DECLARE RESULT INT;
SET A_ID = JSON_Get_Id(A_JSON);
SET B_ID = JSON_Get_Id(B_JSON);
SELECT COUNT(A.id) INTO RESULT
FROM intervals `A`
INNER JOIN intervals `B`
ON B.id = B_ID
WHERE
A.id = A_ID
AND A.`from` >= B.`from`
AND A.`to` <= B.`to`
;
RETURN RESULT = 1;
END $$