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

count, datetime, mysql, popularity

Solution

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

Problem

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?

Original source