MySQL: Getting "busiest" or "most popular" hour from a datetime field?

Question

Consider the following table which has the fields - id (int) and date_created (datetime):

id       date_created

 1       2010-02-25 12:25:32
 2       2010-02-26 13:40:37
 3       2010-03-01 12:02:22
 4       2010-03-01 12:10:23
 5       2010-03-02 10:10:09
 6       2010-03-03 12:45:03

I want to know the busiest/most popular hour of the day for this set of data. In this example, the result I'm looking for would be 12.

Ideas?

score 7 · Answer 1 · answered Mar 18 '10 at 16:39

7

To get just the most popular hour, use this query

select date_format( date_created, '%H' ) as `hour`
  from [Table]
 group by date_format( date_created, '%H' )
 order by count(*) desc
 limit 1;

If you want to look at all the data, go with this one

select count(*) as num_records
     , date_created
     , date_format( date_created, '%H' ) as `hour`
  from [Table]
 group by `hour`
 order by num_records desc;

answered Mar 18 '10 at 16:39

Peter Bailey

105,256
31
182
206

Any advantage of using date_format() over hour() or vice-versa? – k00k Mar 18 '10 at 16:45
No, I don't think so. It was just the function I was already familiar with off the top of my head. – Peter Bailey Mar 18 '10 at 16:49
That's the same reason I used hour(). – Simon Mar 18 '10 at 17:19

Brent Baisley · Answer 2 · 2014-02-22T13:40:56.603

4

If you want something a little more flexible, perhaps to the half hour, or quarter hour, you can do the following:

SELECT floor(time_to_sec(date_created)/3600),count(*) AS period 
FROM table GROUP BY period ORDER BY c DESC

If you want the most popular 2 hour interval, use 7200. The most popular 15 minute interval, use 900. You just need to remember you are dealing with seconds (3600 seconds in an hour).

edited Feb 22 '14 at 13:40

answered Mar 18 '10 at 16:54

Brent Baisley

12,641
2
26
39

This throws an error because interval is a comparison function ( http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_interval ). Changing the word interval to foo works, but the results are not correct. – k00k Mar 18 '10 at 17:03
of course, forget about interval. I left off the count, so it couldn't order it correctly (whoops). – Brent Baisley Mar 19 '10 at 10:24
@k00k: The error in the query above is very simple. The second selection `COUNT(*)` has two aliases, which is a problem. Move the alias 'period' to the first selected field, so one has 'c' and one has 'period'. – TheCarver Feb 22 '14 at 12:41
@BrentBaisley, your COUNT(*) has two aliases by mistake. – TheCarver Feb 22 '14 at 12:42

score 3 · Answer 3 · answered Mar 18 '10 at 16:36

3

Use the hour() function to extract the hour, then do the usual aggregation:

SELECT count(hour(date_created)) AS c, hour(date_created) AS h FROM table GROUP BY h ORDER BY c DESC;

answered Mar 18 '10 at 16:36

Simon

12,018
4
34
39

score 3 · Accepted Answer · answered Mar 18 '10 at 17:28

I like both Simon and Peter's answers, but I can't select both as accepted. I combined the 2 to make a cleaner query that only returned the popular hour (I don't need the counts).

SELECT hour(date_created) AS h 
FROM my_table 
GROUP BY h 
ORDER BY count(*) DESC 
LIMIT 1

score 0 · Answer 5 · answered Mar 18 '10 at 16:39

0

You could try this:

SELECT 
  DATE_FORMAT(date,'%H') as hours, 
  count(*) as count 
FROM 
  myTable 
GROUP BY 
  hours 
ORDER BY 
  count DESC

answered Mar 18 '10 at 16:39

jochil

1,074
6
12

MySQL: Getting "busiest" or "most popular" hour from a datetime field?

5 Answers5