MySQL Forums
Forum List  »  Newbie

Re: scanning for data gaps in time series
Posted by: Peter Brawley
Date: July 15, 2011 07:18AM

It's really just another version of "Find available bookings:

drop table if exists 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 |
+---------+---------------------+---------------------+

PB

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.