Gaps in a time series in MySQL

-- ---------------------------------------------------------------------------
-- Gaps in a time series
--
-- from the Artful Common Queries page
-- http://www.artfulsoftware.com/infotree/qrytip.php?id=72
-- ---------------------------------------------------------------------------

-- 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,'2011-7-1 08:00:00', '2011-7-1 10:00:00');
insert into jobtimes values(2,1,'2011-7-1 11:00:00', '2011-7-1 14:00:00');
insert into jobtimes values(3,2,'2011-7-1 08:00:00', '2011-7-1 09:00:00');
insert into jobtimes values(4,2,'2011-7-1 09:00:00', '2011-7-1 10:00:00');
insert into jobtimes values(5,3,'2011-7-1 08:00:00', '2011-7-1 08:30:00');
insert into jobtimes values(6,3,'2011-7-1 10:00:00', '2011-7-1 12:00:00');

select * from jobtimes;
-- +------+---------+---------------------+---------------------+
-- | id   | machine | start_time          | stop_time           |
-- +------+---------+---------------------+---------------------+
-- |    1 |       1 | 2011-07-01 08:00:00 | 2011-07-01 10:00:00 |
-- |    2 |       1 | 2011-07-01 11:00:00 | 2011-07-01 14:00:00 |
-- |    3 |       2 | 2011-07-01 08:00:00 | 2011-07-01 09:00:00 |
-- |    4 |       2 | 2011-07-01 09:00:00 | 2011-07-01 10:00:00 |
-- |    5 |       3 | 2011-07-01 08:00:00 | 2011-07-01 08:30:00 |
-- |    6 |       3 | 2011-07-01 10:00:00 | 2011-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 | 2011-07-01 10:00:00 | 2011-07-01 11:00:00 |
-- |       3 | 2011-07-01 08:30:00 | 2011-07-01 10:00:00 |
-- +---------+---------------------+---------------------+