T-SQL Time Averaging

Question

I have a table in SQL Server that stores statistics for a piece of hardware, rows in the table represent data for a given second. It contains for example, these columns:

timestamp (DateTime)
value (int)

What I want to do is select the data from the table for a given date/time range but return it in such a way that it averages for a given time period (such as 1 minute, 5 minute, 1 day etc) between the given range. So for an hour I'd have 60 rows of 1 minute averages.

Where do I start with this? Anybody any points or ideas?

Answer 1

You can do a select and group by on a DatePart of your timestamp.

For Example:

SELECT
    DATEPART(hh, [timestamp]),
    DATEPART(mi, [timestamp]),
    AVG([value])
FROM
    YourTable
WHERE
    [timestamp] BETWEEN '2009-01-01 00:00:00.000' AND '2009-02-01 00:00:00.000'
GROUP BY
    DATEPART(hh, [timestamp]),
    DATEPART(mi, [timestamp])

EDIT: For your more complex time spans like 5 mins, you can do a divide on the datepart as follows.

DATEPART(mi, [timestamp]) / 5 * 5

Answer 2

WITH    cal(m) AS
        (
        SELECT  1
        UNION ALL
        SELECT  m + 1
        FROM    cal
        WHERE   m < 60
        )
SELECT  DATEADD(minute, m, @start), AVG(value)
FROM    cal
LEFT JOIN
        timestamp
ON      timestamp > DATEADD(minute, m, @start)
        AND timestamp <= DATEADD(minute, m + 1, @start)
GROUP BY
        m

This will select averages for all minutes inside a given hour, even those for which there are no records.