将DateTime分为5、15、30和60分钟间隔 [英] Group DateTime into 5,15,30 and 60 minute intervals

问题描述

我正在尝试将一些记录分为5分钟，15分钟，30分钟和60分钟间隔：

I am trying to group some records into 5-, 15-, 30- and 60-minute intervals:

SELECT AVG(value) as "AvgValue",
sample_date/(5*60) as "TimeFive"
FROM DATA
WHERE id = 123 AND sample_date >= 3/21/2012

我想运行几个查询，每个查询会将我的平均值分组为所需的时间增量。因此5分钟查询将返回如下结果：

i want to run several queries, each would group my average values into the desired time increments. So the 5-min query would return results like this:

AvgValue  TimeFive
6.90      1995-01-01 00:05:00
7.15      1995-01-01 00:10:00
8.25      1995-01-01 00:15:00

30分钟的查询将导致以下结果：

The 30-min query would result in this:

AvgValue  TimeThirty 
6.95      1995-01-01 00:30:00
7.40      1995-01-01 01:00:00

datetime 列的格式为 yyyy-mm-dd hh：mm：ss 格式

我在 datetime 列中收到隐式转换错误。

I am getting implicit conversion errors of my datetime column. Any help is much appreciated!

推荐答案

使用

datediff(minute, '1990-01-01T00:00:00', yourDatetime)

将为您提供1990-1-1年以来的分钟数（您可以使用所需的基准日期）。

will give you the number of minutes since 1990-1-1 (you can use the desired base date).

然后可以除以5、15 30或60，然后按此除法结果分组。
我已将其作为整数除法进行求值，因此您将获得一个整数，可用于分组。

Then you can divide by 5, 15, 30 or 60, and group by the result of this division. I've cheked it will be evaluated as an integer division, so you'll get an integer number you can use to group by.

ie

group by datediff(minute, '1990-01-01T00:00:00', yourDatetime) /5

更新由于原始问题已被编辑为要求数据在日期之后以日期时间格式显示在分组中，我添加了一个简单的查询，该查询将执行OP想要的操作：

UPDATE As the original question was edited to require the data to be shown in date-time format after the grouping, I've added this simple query that will do what the OP wants:

-- This convert the period to date-time format
SELECT 
    -- note the 5, the "minute", and the starting point to convert the 
    -- period back to original time
    DATEADD(minute, AP.FiveMinutesPeriod * 5, '2010-01-01T00:00:00') AS Period,
    AP.AvgValue
FROM
    -- this groups by the period and gets the average
    (SELECT
        P.FiveMinutesPeriod,
        AVG(P.Value) AS AvgValue
    FROM
        -- This calculates the period (five minutes in this instance)
        (SELECT 
            -- note the division by 5 and the "minute" to build the 5 minute periods
            -- the '2010-01-01T00:00:00' is the starting point for the periods
            datediff(minute, '2010-01-01T00:00:00', T.Time)/5 AS FiveMinutesPeriod,
            T.Value
        FROM Test T) AS P
    GROUP BY P.FiveMinutesPeriod) AP

注意：I为了清楚起见，将其分为3个子查询。您应该从内而外阅读它。当然，它可以写为一个简单的查询

注意：如果您更改时间段和开始日期时间，可以获取您需要的任何时间间隔，例如从给定日期开始的几周，或者您需要的任何时间

如果要生成此查询的测试数据

If you want to generate test data for this query use this:

CREATE TABLE Test
( Id INT IDENTITY PRIMARY KEY,
Time DATETIME,
Value FLOAT)

INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:00:22', 10)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:03:22', 10)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:04:45', 10)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:07:21', 20)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:10:25', 30)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:11:22', 30)
INSERT INTO Test(Time, Value) VALUES('2012-03-22T00:14:47', 30)

执行查询的结果是这样的：

The result of executing the query is this:

Period                     AvgValue
2012-03-22 00:00:00.000    10
2012-03-22 00:05:00.000    20
2012-03-22 00:10:00.000    30

这篇关于将DateTime分为5、15、30和60分钟间隔的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！