BlueBird BlueBird - 3 years ago 111
MySQL Question

Group by hour for given time range

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download