Group by hour for given time range

Question

Several solutions were found for grouping by the hour, given a single DATETIME field. In my case, I have a DATETIME range where I need to group the result into hourly and get the count.

I will try to illustrate my table structure below. My data table is as follows.

mysql> select * from access_logger;
+---------+---------------------+---------------------+
| user_id | entered_at          | exit_at             |
+---------+---------------------+---------------------+
|   20178 | 2017-09-11 07:02:35 | 2017-09-11 10:10:09 |
|   18998 | 2017-09-11 08:02:35 | 2017-09-11 08:41:45 |
|    6754 | 2017-09-11 08:02:35 | 2017-09-11 12:06:42 |
|   18998 | 2017-09-11 09:02:35 | 2017-09-11 13:30:43 |
| // results continues....                            |
+---------+---------------------+---------------------+

based on the above table structure I want to see how many users were connected to the system in each hour. The expected result is like...

+------+-------+
|hours | count |
+------+-------+
|    7 |     1 |
|    8 |     2 |
|    9 |     3 |
|   10 |     3 |
|   11 |     2 |
|   12 |     2 |
|   13 |     1 |
+------+--------

I created a query that fetches the result for each hour independently.

mysql> select "10" as hours, count(user_id) as count 
    -> from access_logger 
    -> where hour(entered_at) <=10 and hour(exit_at) >= 10;

+------+-------+
|hours | count |
+------+-------+
|   10 |     3 |
+------+--------

The above query will get the output for only a single hour group. How can I compose a query that creates the output for all 24 hours in 24 rows?

Answer 1

Provided your table(or any other table of yours) has more than 24 rows you could combine your query and

SELECT  @N := @N +1 AS hour 
FROM access_logger , (SELECT @N:=-1) dum LIMIT 23;

found at SQL SELECT to get the first N positive integers

i.E.

select a.hour, count(b.user_id) as count 
from access_logger b inner join (SELECT  @N := @N +1 AS hour 
      FROM access_logger , (SELECT @N:=-1) dum LIMIT 23) a on 
hour(entered_at) <= a.hour and hour(exit_at) >= a.hour;

Sorry, got no mysql at hand, so this is not tested

Answer 2

This is a crazy engineering answer, use a temporary table and UNION ALL query. This certainly does not work for millions of rows. Temporary tables are alive for the duration of sql session. You may want to drop it after the query if application server uses a connection pool.

DROP TEMPORARY TABLE IF EXISTS aTemp;
CREATE TEMPORARY TABLE aTemp ENGINE=memory AS
 (Select hour(entered_at) as enteredH, hour(exited_at) as exitedH From access_logger);

Select '0' as 'hour', count(*) as 'count' From aTemp Where (0 between enteredH and exitedH)
UNION ALL
Select '1' as 'hour', count(*) as 'count' From aTemp Where (1 between enteredH and exitedH)
UNION ALL
Select '2' as 'hour', count(*) as 'count' From aTemp Where (2 between enteredH and exitedH)
UNION ALL
Select '3' as 'hour', count(*) as 'count' From aTemp Where (3 between enteredH and exitedH)
UNION ALL
Select '4' as 'hour', count(*) as 'count' From aTemp Where (4 between enteredH and exitedH)
UNION ALL
Select '5' as 'hour', count(*) as 'count' From aTemp Where (5 between enteredH and exitedH)
UNION ALL
Select '6' as 'hour', count(*) as 'count' From aTemp Where (6 between enteredH and exitedH)
UNION ALL
Select '7' as 'hour', count(*) as 'count' From aTemp Where (7 between enteredH and exitedH)
UNION ALL
Select '8' as 'hour', count(*) as 'count' From aTemp Where (8 between enteredH and exitedH)
UNION ALL
Select '9' as 'hour', count(*) as 'count' From aTemp Where (9 between enteredH and exitedH)
UNION ALL
Select '10' as 'hour', count(*) as 'count' From aTemp Where (10 between enteredH and exitedH)
UNION ALL
Select '11' as 'hour', count(*) as 'count' From aTemp Where (11 between enteredH and exitedH)
UNION ALL
Select '12' as 'hour', count(*) as 'count' From aTemp Where (12 between enteredH and exitedH)
UNION ALL
Select '13' as 'hour', count(*) as 'count' From aTemp Where (13 between enteredH and exitedH)
UNION ALL
Select '14' as 'hour', count(*) as 'count' From aTemp Where (14 between enteredH and exitedH)
UNION ALL
Select '15' as 'hour', count(*) as 'count' From aTemp Where (15 between enteredH and exitedH)
UNION ALL
Select '16' as 'hour', count(*) as 'count' From aTemp Where (16 between enteredH and exitedH)
UNION ALL
Select '17' as 'hour', count(*) as 'count' From aTemp Where (17 between enteredH and exitedH)
UNION ALL
Select '18' as 'hour', count(*) as 'count' From aTemp Where (18 between enteredH and exitedH)
UNION ALL
Select '19' as 'hour', count(*) as 'count' From aTemp Where (19 between enteredH and exitedH)
UNION ALL
Select '20' as 'hour', count(*) as 'count' From aTemp Where (20 between enteredH and exitedH)
UNION ALL
Select '21' as 'hour', count(*) as 'count' From aTemp Where (21 between enteredH and exitedH)
UNION ALL
Select '22' as 'hour', count(*) as 'count' From aTemp Where (22 between enteredH and exitedH)
UNION ALL
Select '23' as 'hour', count(*) as 'count' From aTemp Where (23 between enteredH and exitedH)
;

DROP TEMPORARY TABLE IF EXISTS aTemp;

You may add any pre-filters to (Select hour(entered_at)... From access_logger Where...) query.

Answer 3

You need to use group by HOUR

SELECT HOUR(entered_at) AS Hours, COUNT(user_id) AS Total_Users    
FROM access_logger
GROUP BY HOUR(entered_at);

Edited ==

You could create a store procedure where you will be counting the total user for each hour between start_time and end_time

begin
p1 INT;
SET p1=1;
counter LOOP

    select @p1 as Hours, count(*) as total_user 
    from access_logger 
    where @p1 between hour(start) and hour(end) ;

    SET p1 = p1 + 1;

    IF p1=24 THEN
       LEAVE counter;
    END IF;
END LOOP counter;
end;