title: Sum amount of overlapping datetime ranges in MySQL
body: <p>I have a table of events, each with a StartTime and EndTime (as type DateTime) in a MySQL Table. </p>
  
  <p>I'm trying to output the <strong>sum of overlapping times</strong> and the number of events that overlapped.</p>
  
  <p>What is the most efficient / simple way to perform this query in MySQL?</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `events` (
    `EventID` int(10) unsigned NOT NULL auto_increment,
    `StartTime` datetime NOT NULL,
    `EndTime` datetime default NULL,
    PRIMARY KEY  (`EventID`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=37 ;
  
  
  INSERT INTO `events` (`EventID`, `StartTime`, `EndTime`) VALUES
  (10001, '2009-02-09 03:00:00', '2009-02-09 10:00:00'),
  (10002, '2009-02-09 05:00:00', '2009-02-09 09:00:00'),
  (10003, '2009-02-09 07:00:00', '2009-02-09 09:00:00');
  
  
  # if the query was run using the data above,
  # the table below would be the desired output
  
  # Number of Overlapped Events | Total Amount of Time those events overlapped.
  1, 03:00:00
  2, 02:00:00
  3, 02:00:00
  </code></pre>
  
  <p>The purpose of these results is to generate a bill for hours used. (if you have one event running, you might pay 10 dollars per hour. But if two events are running, you only have to pay 8 dollars per hour, but only for the period of time you had two events running.)</p>