T-SQL Group by n-minutes

There are literally hundreds if not thousands of articles on the internet explaining how to group by n-minutes, most of them utilizes this technique:

DATEADD(MINUTE, @interval *(DATEDIFF(MINUTE, '20000101', sampletime) / @interval), '20000101')  

Above will round sampletime to nearest 10 minutes interval which is desired in some cases, but if you e.g. use aggregate functions, your result will only be correct each n’th minute. Therefore I have created this little query which do not round, and is excellent for graphing time sensitive data.

DECLARE @starttime DATETIME = GETDATE() -- Start grouping from here
DECLARE
@lookback INT = 120 -- Group backwards 120 minutes from start
DECLARE
@interval INT = 20 -- Group in intervals of 20 minutes

SELECT
(t.sampleinterval * @interval) AS [+ Min], AVG(querydc) AS [QueryDC]
FROM
(SELECT FLOOR(DATEDIFF(MI, sampletime, @starttime) / @interval) AS sampleinterval, querydc
       FROM dbo.mylogtable
       WHERE (sampletime >= DATEADD(MI, -(@lookback-1), @starttime)) AND(sampletime <= @starttime)) AS t

GROUP
by t.sampleinterval
ORDER
by t.sampleinterval

Below  is the output it generates, if  @starttime is e.g. 12:13 then the first row will show the average value for QueryDC in the interval between 12:13 and 11:53, and the second row in the interval between 11:53 and 11:33 etc. etc.

+ Min     QueryDC
-----------------
0         64
20        70
40        69
60        90
80        102
100       157

Month List

RecentPosts