Select date/time groupings in MySQL grouped by hour

Question

I have a bunch of date data in a mysql table like this:

2010-02-13 1:00:00, "soma data"
2010-02-13 1:25:00, "soma data"
2010-02-13 1:37:00, "soma data"
2010-02-13 2:12:00, "soma data"

I want to select a report which shows the data grouped by hour, for example:

On Feb 13, during the hour from 1:00 pm to 1:59 pm, there were 3 data points.
On Feb 13, during the hour from 2:00 pm to 2:59 pm, there was 1 data points.
...

Basically i want to report the cumulative amount of records which occurred during every hour of the day. So the end result would give me a report of say 10 days, broken out in 24 hour increments, so I can see how much data there is during any given hour on any given day.

TIA, Hope you can help!

Answer 1

You may want to use the GROUP BY clause as in the following query:

SELECT 
    COUNT(*),
    YEAR(dateTimeField),
    MONTH(dateTimeField),
    DAY(dateTimeField),
    HOUR(dateTimeField)
FROM 
    yourTable
WHERE
    dateTimeField >= '2010-02-04 00:00:00' AND
    dateTimeField < '2010-02-14 00:00:00'
GROUP BY 
    YEAR(dateTimeField), 
    MONTH(dateTimeField),
    DAY(dateTimeField),
    HOUR(dateTimeField);

You may also want to check the MySQL documentation for further reference on date and time functions:

• MySQL 5.1 Reference Manual - Date and Time Functions

Answer 2

SELECT HOUR(datetime), COUNT(*)
FROM table
GROUP BY HOUR(datetime);

You will get two fields: the hour and the number of events during that hour.