sql - Group DateTime into 5,15,30 and 60 minute intervals

Question

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

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

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

The datetime column is in yyyy-mm-dd hh:mm:ss format

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

Answer 1

Using

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

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

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.

i.e.

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

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

NOTE: I've divided this in 3 subqueries for clarity. You should read it from inside out. It could, of course, be written as a single, compact query

NOTE: if you change the period and the starting date-time you can get any interval you need, like weeks starting from a given day, or whatever you can need

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