MySQL Query GROUP BY and COUNT based on input interval + taking in account two table colums as interval

I'm writing a query that will fetch results from a logging table, resulting in a overview of how many visitors that were logged on my webpage at a specified interval, taking in account their actual login time (login_time, logout_time) and the input GROUP by parameter. (DAY(),HOUR() or MINUTE())

I've twisted my brain around this for a few days on and off. Really need some positive input from anyone interested.

Input

So basicl开发者_运维知识库y what I would input in my query would be what time interval I would like to search by. Specifying how the result would be grouped like. (DAY(),HOUR() or MINUTE())

Example data

ID| userID | login_time          | logout_time
------------------------------------------------------
1 | 1      | 06.11.2010 16:57:16 | 06.11.2010 16:34:11
2 | 2      | 06.11.2010 16:47:11 | 06.11.2010 19:55:15
3 | 3      | 06.11.2010 16:33:16 | 06.11.2010 16:53:33
4 | 4      | 06.11.2010 16:13:25 | 06.11.2010 18:54:54
5 | 5      | 06.11.2010 16:02:16 | 06.11.2010 16:34:11
6 | 6      | 06.11.2010 16:00:11 | 06.11.2010 17:55:19
7 | 6      | 06.11.2010 19:00:11 | 06.11.2010 22:55:19
8 | 6      | 06.11.2010 20:00:11 | 06.11.2010 23:55:19
9 | 6      | 06.11.2010 20:00:11 | 06.11.2010 21:55:19
9 | 6      | 06.11.2010 09:00:11 | 06.11.2010 10:00:19

Prefered result

Example Input: Between 06.11.2010 16:30:00 and 06.11.2010 16:35:00 and grouped by MINUTE()

Count | Date
---------------------------
5     | 06.11.2010 16:30:00
5     | 06.11.2010 16:31:00
5     | 06.11.2010 16:32:00
4     | 06.11.2010 16:33:00
5     | 06.11.2010 16:34:00
3     | 06.11.2010 16:35:00

Example Input: Between 06.11.2010 00:30:00 and 06.11.2010 23:35:00 and grouped by HOUR()

Count | Date
---------------------------
1     | 06.11.2010 09:00:00
1     | 06.11.2010 10:00:00

--> PS: I don't mind having a gap here if there is intervals missing (ie. no data)

6     | 06.11.2010 16:00:00
3     | 06.11.2010 17:00:00
2     | 06.11.2010 18:00:00
2     | 06.11.2010 19:00:00
3     | 06.11.2010 20:00:00
3     | 06.11.2010 21:00:00
2     | 06.11.2010 22:00:00
1     | 06.11.2010 23:00:00

Maybe you could illustrate a suitable and effective query that will yield the expected results I have in mind? I'll leave my cluttering code out from this post for the moment.

---

Sorry, there's no way using a single query. Because SQL is based on set theory you have to create a set of your time intervals.

You can do this either in a temporary table or in a stored procedure which calculates the time intervals and executes a query for each interval.

So you can do:

CREATE TEMPORARY TABLE temp_intervals (
    start_datetime DATETIME NOT NULL ,
    end_datetime DATETIME NOT NULL
)

INSERT INTO temp_intervals(start_datetime, end_datetime) VALUES
    ('2010-11-06 16:30:00', '2010-11-06 16:31:00'),
    ('2010-11-06 16:31:00', '2010-11-06 16:32:00')
    ...

Then execute the query:

SELECT start_datetime, COUNT(*)
FROM temp_intervals
LEFT JOIN login_history -- LEFT JOIN if you want empty intervals in the result
WHERE login_time <= start_datetime AND logout_time > end_datetime
GROUP BY start_datetime

Remember that all statements have to be done in one connection, as the temporary table is only visible to that connection.

Also, you don't need any procedural language because you can have MySQL create the temp table using a stored procedure.

---

The easiest way to do this in SQL is to create a temporary table with all the time values you want to test, and then join it to the log table.

drop table if exists minutes;
create temporary table minutes (date datetime not null);
set @t := '2010.11.06 16:30:00';  insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);
set @t := @t + interval 1 minute; insert into minutes values (@t);

select count(*) as count, m.date
from minutes m join log l
on m.date <= l.logout_time and m.date + interval 1 minute >= l.login_time
group by m.date;