GROUP BY date range

Question

CREATE TABLE `connectionLog` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `serverTimeConnected` datetime NOT NULL,
  `serverTimeDisconnected` datetime  NOT NULL,
  PRIMARY KEY (`ID`)
);

with this query i get all IDs where serverTimeConnected is in one hour.

SELECT  HOUR( `serverTimeConnected`) as STUNDE, 
    count(`ID`) as HITS 
FROM    `connectionLog` 
GROUP BY HOUR( `serverTimeConnected`)

but i need all IDs between serverTimeConnected and serverTimeDisconnected.

ID  serverTimeConnected serverTimeDisconnected
1   10:00:00            10:10:00    
2   10:00:00            11:10:00    
3   10:00:00            12:10:00

I need a result - like

STUNDE  HITS
10,     3
11,     2
12,     1

Answer 1

UPDATE: I've found this solution

 SELECT Stunden.ID, COUNT(Stunden.ID) as HITS FROM Stunden
INNER JOIN connectionLog cl 
  ON (
    (ADDTIME(DATE_FORMAT(cl.serverTimeConnected,'%Y-%m-%d 00:00:00'),CONCAT(Stunden.ID,":00:00")) 
      BETWEEN cl.serverTimeConnected AND cl.serverTimeDisconnected)
    OR
      (ADDTIME(DATE_FORMAT(cl.serverTimedisConnected,'%Y-%m-%d 00:00:00'),CONCAT(Stunden.ID,":00:00")) 
      BETWEEN cl.serverTimeConnected AND cl.serverTimeDisconnected)
     )

GROUP BY Stunden.ID

http://sqlfiddle.com/#!2/6f3b0/11 (updated)

The table Stunden is a list of all distinct hours available. A simple INNER JOIN when the hour is between connection and disconnection (with a look at the day) get us the number of match that we searching. Then Group by hours and we match the right HITS.