mysql 如何时间分组,选择MySQL中的日期/时间分组按小时分组

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: