MySQL query to find hour of the day when the number of housing society visits is highest

Question

Housing Society Visit Data

Id	Contact	Entry_time	Exit_time	Duration of Stay
1	8080808080	26/07/2021 08:00:05	26/07/2021 08:23:06	181
2	9692596925	26/07/2021 08:12:49	26/07/2021 08:14:44	115
3	7099270992	26/07/2021 11:02:49	26/07/2021 11:14:44	715
4	8900289002	26/07/2021 16:12:49	26/07/2021 16:14:44	115
5	9089590895	26/07/2021 15:12:49	26/07/2021 15:14:44	115
6	8765087650	26/07/2021 19:12:49	26/07/2021 19:14:44	115
7	7862178621	26/07/2021 18:12:49	26/07/2021 18:14:44	115

Visit data is available for many years and can contain millions of rows, so the solution should have a low time complexity.
Expected output: 8-9 AM (since highest number of visits (2) are made during that hour).

What have you tried, with which results and with what expected outcome? — Firewizz, Dec 01 '21 at 14:25
What if a visit last 3 hours should all 3 hours be included in separate hour counts? — P.Salmon, Dec 01 '21 at 14:37
Edit the question and post the data as text not as image, so it can be easier to reproduce the problem — Ergest Basha, Dec 01 '21 at 14:39
@P.Salmon No I just need the hour with the maximum occurrences. — GeekyCoder, Dec 02 '21 at 15:06
@ErgestBasha I have removed the image of the table and added the table. — GeekyCoder, Dec 02 '21 at 15:08

Firewizz · Answer 1 · 2021-12-03T08:14:38.857

As I read the question this would help you very much on the way. There are a couple of things you can do to improve this tho:

Create separate table with hours to avoid the unions (or do it with numbers table);
Store times separatly, the TIME() method slows the query.

.

SELECT 
    hours.`hour`,
    COUNT(occurrence.perfId) AS occurrences
FROM (
    SELECT '00:00' AS `hour`
    UNION SELECT '01:00'
    UNION SELECT '02:00'
    UNION SELECT '03:00'
    UNION SELECT '04:00'
    UNION SELECT '05:00'
    UNION SELECT '06:00'
    UNION SELECT '07:00'
    UNION SELECT '08:00'
    UNION SELECT '09:00'
    UNION SELECT '10:00'
    UNION SELECT '11:00'
    UNION SELECT '12:00'
    UNION SELECT '13:00'
    UNION SELECT '14:00'
    UNION SELECT '15:00'
    UNION SELECT '16:00'
    UNION SELECT '17:00'
    UNION SELECT '18:00'
    UNION SELECT '19:00'
    UNION SELECT '20:00'
    UNION SELECT '21:00'
    UNION SELECT '22:00'
    UNION SELECT '23:00'    
) hours
LEFT JOIN YOUR_TABLE occurrence ON hours.`hour` BETWEEN TIME(occurrence.Entry_time) AND TIME(occurrence.Exit_time)
GROUP BY hours.`hour`
ORDER BY occurrences DESC

Would produce (Data from a test database):

[hour] [occurrences]
10:00   73554
11:00   67492
09:00   65679
08:00   63886
13:00   63565
12:00   62525
07:00   61500
14:00   53095
15:00   49017
16:00   41955
17:00   31991
18:00   21251
06:00   17591
19:00   13717
20:00   8532
21:00   4421
22:00   2050
23:00   818
05:00   796
04:00   561
01:00   175
03:00   123
02:00   120
00:00   23

To get the hour only just wrap with a query and select the first:

SELECT hour FROM (
    # Paste query from above here...
) hourData
LIMIT 1;

Noted. But for the time being, can you just help me with this table? Also, I just need the hour with the maximum occurrences (not the count of occurrences) to be printed as the output which is 8-9AM for my table. — GeekyCoder, Dec 02 '21 at 15:03
Not per day, I need maximum occurrences from the entire table. — GeekyCoder, Dec 03 '21 at 08:47
But tell me if I am wrong but isn't the count exactly the number of occurrences? — Firewizz, Dec 03 '21 at 10:10

score 0 · Accepted Answer · answered Dec 04 '21 at 12:03

I was able to get the required output using the following query:
SELECT HOUR(Entry_time) as hr FROM table_name GROUP BY HOUR(Entry_time) ORDER BY COUNT(*) DESC LIMIT 1;
Thanks to the solution by @nicholascarey: SQL to determine peak volume and hour for all days

MySQL query to find hour of the day when the number of housing society visits is highest

2 Answers2