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!
解决方案
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: