Gaps in a time series

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.Here is a query that shows the start times following breaks in activity for a given machine.



SELECT 

  id,

  machine AS thismachine,

  start_time AS StartAfterGap

FROM jobtimes

WHERE id > 1 AND NOT EXISTS (

  SELECT stop_time 

  FROM jobtimes

  WHERE machine=thismachine

    AND start_time < StartAfterGap 

    AND stop_time >= StartAfterGap 

)