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?

Basically, I don't want the date in result instead I need it to see if the user falls within given hour. — Muneer, Oct 01 '17 at 07:54
You can explore [temportal database](https://en.wikipedia.org/wiki/Temporal_database) if `mySQL` support that — zarruq, Oct 01 '17 at 09:30

Ravi · Answer 1 · 2017-10-01T08:51:55.470

1

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;

edited Oct 01 '17 at 08:51

answered Oct 01 '17 at 07:54

Ravi

30,829
42
119
173

This is a good solution If have a single datetime field and need to see if the user login falls into the given hour. In my case, I need to see if user's duration falls into the given hour. Any user enters at 10:00 and exits at 12:00 will not fall into hour (11) as per this query. – Muneer Oct 01 '17 at 07:58
@BlueBird I didn't get you. Could you post this requirement with explanation ? – Ravi Oct 01 '17 at 07:58
if you look at my question above. I have given sample records and the expected result. It describes all. For example, say you are entering at morning 10 AM and exit at evening 2:30 PM. So you will fall in all (10, 11, 12, 13 and 14) hours. Did you get me point? – Muneer Oct 01 '17 at 08:01
@BlueBird Not sure, if that is possible in SQL – Ravi Oct 01 '17 at 08:09
@BlueBird you could check my post. I have not tested it. But, it will give you idea and feel free to update my post. – Ravi Oct 01 '17 at 08:47

Turo · Accepted Answer · 2017-10-01T09:40:26.257

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

edited Oct 01 '17 at 09:40

answered Oct 01 '17 at 09:28

Turo

4,724
2
14
27

This query works great. I just add some more salt. `select a.hour, count(b.mac) as count from ezc_device_history b inner join (SELECT @N := @N +1 AS hour FROM ezc_device_history , (SELECT @N:=-1) dum LIMIT 24) a on hour(entered_at) <= a.hour and hour(exit_at) >= a.hour group by a.hour;` – Muneer Oct 01 '17 at 11:24

Whome · Answer 3 · 2017-10-01T09:27:23.243

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.

Group by hour for given time range

3 Answers3