3

I have a question that is almost the same as Sum amount of overlapping datetime ranges in MySQL, so I'm reusing part of his text, hope that is ok...

I have a table of events, each with a StartTime and EndTime (as type DateTime) in a MySQL Table.

I'm trying to output the sum of overlapping times for each type of event and the number of events that overlapped.

What is the most efficient / simple way to perform this query in MySQL?

CREATE TABLE IF NOT EXISTS `events` (
  `EventID` int(10) unsigned NOT NULL auto_increment,
  `EventType` int(10) unsigned NOT NULL,
  `StartTime` datetime NOT NULL,
  `EndTime` datetime default NULL,
  PRIMARY KEY  (`EventID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;


INSERT INTO `events` (`EventID`, EventType,`StartTime`, `EndTime`) VALUES
(10001,1, '2009-02-09 03:00:00', '2009-02-09 10:00:00'),
(10002,1, '2009-02-09 05:00:00', '2009-02-09 09:00:00'),
(10003,1, '2009-02-09 07:00:00', '2009-02-09 09:00:00'),
(10004,3, '2009-02-09 11:00:00', '2009-02-09 13:00:00'),
(10005,3, '2009-02-09 12:00:00', '2009-02-09 14:00:00');


# if the query was run using the data above,
# the table below would be the desired output

# Number of Overlapped Events , The event type, | Total Amount of Time those events overlapped.
1,1, 03:00:00
2,1, 02:00:00
3,1, 02:00:00
1,3, 01:00:00

There is a really beautiful solution given there by Mark Byers and I'm wondering if that one can be extended to include "Event Type".

His solution without event type was:

SELECT `COUNT`, SEC_TO_TIME(SUM(Duration))
FROM (
    SELECT
        COUNT(*) AS `Count`,
        UNIX_TIMESTAMP(Times2.Time) - UNIX_TIMESTAMP(Times1.Time) AS Duration
    FROM (
        SELECT @rownum1 := @rownum1 + 1 AS rownum, `Time`
        FROM (
            SELECT DISTINCT(StartTime) AS `Time` FROM events
            UNION
            SELECT DISTINCT(EndTime) AS `Time` FROM events
        ) AS AllTimes, (SELECT @rownum1 := 0) AS Rownum
        ORDER BY `Time` DESC
    ) As Times1
    JOIN (
        SELECT @rownum2 := @rownum2 + 1 AS rownum, `Time`
        FROM (
            SELECT DISTINCT(StartTime) AS `Time` FROM events
            UNION
            SELECT DISTINCT(EndTime) AS `Time` FROM events
        ) AS AllTimes, (SELECT @rownum2 := 0) AS Rownum
        ORDER BY `Time` DESC
    ) As Times2
    ON Times1.rownum = Times2.rownum + 1
    JOIN events ON Times1.Time >= events.StartTime AND Times2.Time <= events.EndTime
    GROUP BY Times1.rownum
) Totals
GROUP BY `Count`
Community
  • 1
  • 1
Balroq
  • 487
  • 3
  • 6
  • 17
  • What should happen if two events overlap but they have different event types? – Mark Byers Sep 13 '10 at 11:44
  • They would not count as overlapped as they would be of different types, your solution for the original problem was really beautiful and I you can extend it to include event types it would be perfect for me.. – Balroq Sep 13 '10 at 12:25

1 Answers1

1
SELECT
  COUNT(*) as occurrence
  , sub.event_id
  , SEC_TO_TIME(SUM(LEAST(e1end, e2end) - GREATEST(e1start, e2start)))) as duration
FROM
  (  SELECT  
      , e1.event_id
      , UNIX_TIMESTAMP(e1.starttime) as e1start
      , UNIX_TIMESTAMP(e1.endtime) as e1end
      , UNIX_TIMESTAMP(e2.starttime) as e2start
      , UNIX_TIMESTAMP(e2.endtime) as e2end
    FROM events e1
    INNER JOIN events e2 
      ON (e1.eventtype = e2.eventtype AND e1.id <> e2.id
      AND NOT(e1.starttime > e2.endtime OR e1.endtime < e2.starttime))
  ) sub
GROUP BY sub.event_id 
ORDER BY occurrence DESC
Johan
  • 74,508
  • 24
  • 191
  • 319
  • Fantastic, thank you! However it does not calculate `duration` properly for all cases. You need to change to `SEC_TO_TIME(SUM(LEAST(e1end, e2end) - GREATEST(e1start, e2start)))) as duration`. – happy_marmoset Oct 14 '15 at 12:42