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?

Answer 1

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;

Answer 2

You could try this:

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

Answer 3

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;

Answer 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(*) c 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).

Answer 5

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