选择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.
...

基本上我想报告每次发生的累积记录量一天的小时。所以最终结果会给我一个10天的报告，以24小时为单位进行分解，所以我可以在任何一天的任何一小时内看到有多少数据。

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，希望你能帮助！

TIA, Hope you can help!

答

你可能想使用 GROUP BY 子句如下：

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

您可能还想查看MySQL文档，以便日期和时间功能进一步参考：

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

MySQL 5.1参考手册 - 日期和时间函数

MySQL 5.1 Reference Manual - Date and Time Functions

选择MySQL中的日期/时间分组按小时分组

相关推荐