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

Consider the following tab开发者_如何学Pythonle 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?

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;

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).

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;

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

You could try this:

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