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?

Leukemia

Answer 1

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

Leukemia

Answer 2

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;

Steeve

Any advantage of using date_format() over hour() or vice-versa? – Leukemia

No, I don't think so. It was just the function I was already familiar with off the top of my head. – Steeve

That's the same reason I used hour(). – Broadwater

Answer 3

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

Promotion

This throws an error because interval is a comparison function ( dev.mysql.com/doc/refman/5.0/en/… ). Changing the word interval to foo works, but the results are not correct. – Leukemia

of course, forget about interval. I left off the count, so it couldn't order it correctly (whoops). – Promotion

@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'. – Isador

@BrentBaisley, your COUNT(*) has two aliases by mistake. – Isador

Answer 4

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;

Broadwater

Answer 5

You could try this:

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

Genseric