CODEWITHSUNDEEP
mysqldatetime
user838437
user838437

Reputation: 1501

Most common hour query?

I have this table:

ID(INT)    DATE(DATETIME)

Under the DATE column there are a lot of different dates, and I want to figure out the most common hour between all the rows of the table, regardless of the day.

How can I do that with a MySQL query?

Upvotes: 1

Views: 320

Answers (4)

Marc B
Marc B

Reputation: 360772

SELECT HOUR(date) AS hr, COUNT(*) AS cnt
FROM yourtable
GROUP BY hr
ORDER BY cnt DESC
LIMIT 1

relevant docs: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_hour

Upvotes: 5

wormhit
wormhit

Reputation: 3827

SELECT COUNT( id ) , HOUR( date ) FROM test GROUP BY HOUR( date ) ORDER BY COUNT( id ) DESC LIMIT 1

Upvotes: 0

wachpwnski
wachpwnski

Reputation: 688

You could do a query like:

SELECT COUNT(daterow) AS occurrences FROM table GROUP BY daterow ORDER BY occurrences DESC LIMIT 1;

Upvotes: 0

user1191247
user1191247

Reputation: 12973

Try this -

SELECT HOUR(`DATE`) AS `hour`, COUNT(*)
FROM `table`
GROUP BY `hour`

Upvotes: 2

Related Questions