MySQL: get the "busiest" or "most popular" hour from the datetime field?

Question

Consider the following table, in which there are 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 dataset. In this example, the result I'm looking for will be 12.

Ideas?

Answer 1

I like the answers of Simon and Peter, but I can not choose them as accepted. I combined 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 

Answer 2

To get 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 see all the data, go to this

 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 3

If you need something more flexible, perhaps up to half an hour or a quarter of an 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 need the most popular 2-hour interval, use 7200. The most popular 15-minute interval, use 900. You just need to remember that you are dealing with seconds (3600 seconds per hour).

Answer 4

Use the hour() function to retrieve 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 5

You can try the following:

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