MySQL Query GROUP BY and COUNT based on input interval + two columns as an interval

Question

I am writing a query that will retrieve the results from the logging table, as a result of which the number of visitors who were registered on my web page with a given interval will be displayed, taking into account their actual login time (login_time, logout_time) and input GROUP by parameter. (DAY (), HOUR () or MINUTE ())

I twisted my brain around this for several days. In fact, we need some kind of positive contribution from anyone interested.

Enter

So basically, what I would enter in my query would be the time interval that I would like to look for. Determining how the result will be grouped as. (DAY (), HOUR () or MINUTE ())

Data examples

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

: 06.11.2010 16:30:00 06.11.2010 16:35:00 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

: 06.11.2010 00:30:00 06.11.2010 23:35:00 HOUR()

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

- > PS: , (.. )

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

, , , ? .

Answer 1

SQL - , , .

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;

Answer 2

, . SQL , .

, , .

, :

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')
    ...

:

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

, , .

, , MySQL temp- .