i have database table containing events:
create table events event varchar(32) ,down_time timestamp ,up_time timestamp ,id int unsigned not null auto_increment key ,index(event(16)) ); insert events(event, down_time, up_time) values ('e1', '2015-01-01 00:00:04', '2015-01-01 00:00:08'), ('e2', '2015-01-01 00:00:05', '2015-01-01 00:00:06'), ('e3', '2015-01-01 00:00:02', '2015-01-01 00:00:09'), ('e4', '2015-01-01 00:00:01', '2015-01-01 00:00:07'), ('e5', '2015-01-01 00:00:03', '2015-01-01 00:00:10'); select * events; +-------+---------------------+---------------------+----+ | event | down_time | up_time | id | +-------+---------------------+---------------------+----+ | e1 | 2015-01-01 00:00:04 | 2015-01-01 00:00:08 | 1 | | e2 | 2015-01-01 00:00:05 | 2015-01-01 00:00:06 | 2 | | e3 | 2015-01-01 00:00:02 | 2015-01-01 00:00:09 | 3 | | e4 | 2015-01-01 00:00:01 | 2015-01-01 00:00:07 | 4 | | e5 | 2015-01-01 00:00:03 | 2015-01-01 00:00:10 | 5 | +-------+---------------------+---------------------+----+ 5 rows in set (0.00 sec) 1 2 3 4 5 6 7 8 9 10 | | | | | | | | | | | | | |-------e1------| | | | | | | |-e2| | | | | | |--------------e3-----------| | |----------e4-----------| | | | | | |-------------e5------------| i identify if events overlap @ 1 time. in above intersect here (e2):
overlap |overlap_down_time | overlap_up_time | overlap_duration 1 |2015-01-01 00:00:05 | 2015-01-01 00:00:06 | 00:00:01 i can find overlaps between pairs of events using query not sure how include events ..many in advance !
select e1.event event1_name, e1.down_time event1_down_time, e1.up_time event1_up_time, timediff(e1.up_time, e1.down_time) event1_duration, e2.event event2_name, e2.down_time event2_down_time, e2.up_time event2_up_time, timediff(e2.up_time, e2.down_time) event1_duration, greatest(e1.down_time,e2.down_time) overlap_down_time, least(e1.up_time,e2.up_time) overlap_up_time, timediff( least(e1.up_time,e2.up_time), greatest(e1.down_time,e2.down_time) ) overlap_duration events e1 inner join events e2 on e1.id < e2.id ( e2.down_time <= e1.up_time ) , ( e2.up_time >= e1.down_time ); the solutions proposed below not appear cover case event occurs twice within overall timeframe ..as happens e2 below:
1 2 3 4 5 6 7 8 9 10 | | | |-------e1------| | | | | |---e2--| |-------e2------| | |-------------e3------------| | |----------e4-----------| | | | insert events(event, down_time, up_time) values ('e1', '2015-01-01 00:00:04', '2015-01-01 00:00:08'), ('e2', '2015-01-01 00:00:03', '2015-01-01 00:00:05'), ('e2', '2015-01-01 00:00:06', '2015-01-01 00:00:10'), ('e3', '2015-01-01 00:00:02', '2015-01-01 00:00:09'), ('e4', '2015-01-01 00:00:01', '2015-01-01 00:00:07'); i have been able identify intersect using 2 stage query:
create view overlap1 select concat(a.event,'-', b.event) overlaps, greatest(a.down_time,b.down_time) downtime, least(a.up_time,b.up_time) uptime, time_to_sec(timediff( least(a.up_time,b.up_time), greatest(a.down_time,b.down_time))) duration events join events b on a.event < b.event , (a.event = 'e1' or a.event = 'e2' or a.event = 'e3' or a.event = 'e4') , (b.event = 'e1' or b.event = 'e2' or b.event = 'e3' or b.event = 'e4') ( a.down_time <= b.up_time ) , ( a.up_time >= b.down_time ); select * overlap1; +----------+---------------------+---------------------+----------+ | overlaps | downtime | uptime | duration | +----------+---------------------+---------------------+----------+ | e1-e2 | 2015-01-01 00:00:04 | 2015-01-01 00:00:05 | 1 | | e1-e2 | 2015-01-01 00:00:06 | 2015-01-01 00:00:08 | 2 | | e1-e3 | 2015-01-01 00:00:04 | 2015-01-01 00:00:08 | 4 | | e2-e3 | 2015-01-01 00:00:03 | 2015-01-01 00:00:05 | 2 | | e2-e3 | 2015-01-01 00:00:06 | 2015-01-01 00:00:09 | 3 | | e1-e4 | 2015-01-01 00:00:04 | 2015-01-01 00:00:07 | 3 | | e2-e4 | 2015-01-01 00:00:03 | 2015-01-01 00:00:05 | 2 | | e2-e4 | 2015-01-01 00:00:06 | 2015-01-01 00:00:07 | 1 | | e3-e4 | 2015-01-01 00:00:02 | 2015-01-01 00:00:07 | 5 | +----------+---------------------+---------------------+----------+ create view overlap2 select concat(a.overlaps,'-',b.overlaps) overlaps, greatest(a.downtime,b.downtime) downtime, least(a.uptime,b.uptime) uptime, timediff( least(a.uptime,b.uptime), greatest(a.downtime,b.downtime) ) duration overlap1 join overlap1 b on a.overlaps < b.overlaps , (a.overlaps = 'e1-e2' or a.overlaps = 'e3-e4') , (b.overlaps = 'e1-e2' or b.overlaps = 'e3-e4') ( a.downtime <= b.uptime ) , ( a.uptime >= b.downtime ); select * overlap2; +-------------+---------------------+---------------------+----------+ | overlaps | downtime | uptime | duration | +-------------+---------------------+---------------------+----------+ | e1-e2-e3-e4 | 2015-01-01 00:00:04 | 2015-01-01 00:00:05 | 00:00:01 | | e1-e2-e3-e4 | 2015-01-01 00:00:06 | 2015-01-01 00:00:07 | 00:00:01 | +-------------+---------------------+---------------------+----------+ i achieve single query if possible ..any appreciated !
following query meet requirement:
select e1.* events e1 join events e2 on e1.down_time>=e2.down_time , e1.up_time<=e2.up_time group e1.event having count(*)=(select count(*) events) verify result @ http://sqlfiddle.com/#!9/7c733/4
Comments
Post a Comment