Gaps in a time series

from the Artful Common Queries page

Advanced time series analysis generally requires custom software, but straightforward SQL queries can answer simple time series questions. You have a jobtimes table with columns ID, job, machine, start_time, and stop_time. You wish to know which machines have had gaps between activity periods. It's a version of "Find available booking periods": 
drop table jobtimes;
create table jobtimes(id int, machine smallint, start_time timestamp, stop_time timestamp);
insert into jobtimes values(1,1,'2024-7-1 08:00:00', '2024-7-1 10:00:00');
insert into jobtimes values(2,1,'2024-7-1 11:00:00', '2024-7-1 14:00:00');
insert into jobtimes values(3,2,'2024-7-1 08:00:00', '2024-7-1 09:00:00');
insert into jobtimes values(4,2,'2024-7-1 09:00:00', '2024-7-1 10:00:00');
insert into jobtimes values(5,3,'2024-7-1 08:00:00', '2024-7-1 08:30:00');
insert into jobtimes values(6,3,'2024-7-1 10:00:00', '2024-7-1 12:00:00');
select * from jobtimes;
+------+---------+---------------------+---------------------+
| id   | machine | start_time          | stop_time           |
+------+---------+---------------------+---------------------+
|    1 |       1 | 2024-07-01 08:00:00 | 2024-07-01 10:00:00 |
|    2 |       1 | 2024-07-01 11:00:00 | 2024-07-01 14:00:00 |
|    3 |       2 | 2024-07-01 08:00:00 | 2024-07-01 09:00:00 |
|    4 |       2 | 2024-07-01 09:00:00 | 2024-07-01 10:00:00 |
|    5 |       3 | 2024-07-01 08:00:00 | 2024-07-01 08:30:00 |
|    6 |       3 | 2024-07-01 10:00:00 | 2024-07-01 12:00:00 |
+------+---------+---------------------+---------------------+
SELECT
  a.machine,
  a.stop_time AS 'Unused From',
  Min(b.start_time) AS 'To'
FROM jobtimes AS a 
JOIN jobtimes AS b ON a.machine=b.machine AND a.stop_time < b.start_time
GROUP BY a.stop_time
HAVING a.stop_time < MIN(b.start_time)
ORDER BY machine; 
+---------+---------------------+---------------------+
| machine | Unused From         | To                  |
+---------+---------------------+---------------------+
|       1 | 2024-07-01 10:00:00 | 2024-07-01 11:00:00 |
|       3 | 2024-07-01 08:30:00 | 2024-07-01 10:00:00 |
+---------+---------------------+---------------------+

Last updated 15 Jul 2024