CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Roland Bengtsson
Roland Bengtsson

Reputation: 5158

How can I group by arbitary time period with SQL

This is similar but not equal to my previous question That was about how to summarize log-items per day. I use this SQL.

SELECT 
  [DateLog] = CONVERT(DATE, LogDate), 
  [Sum] = COUNT(*)
FROM PerfRow
GROUP BY CONVERT(DATE, LogDate)
ORDER BY [DateLog];

Now I want to improve that to summarize over an arbitary time period. So instead of sum per day, sum per hour or 5 minutes. Is this possible ?

I use SQL Server 2008 R2

Upvotes: 1

Views: 171

Answers (1)

rickythefox
rickythefox

Reputation: 6851

You can round LogDate using DATEADD and DATEPART and then group by that.

Example (groups by five second intervals):

SELECT 
  [DateLog] = DATEADD(ms,((DATEPART(ss, LogDate)/5)*5000)-(DATEPART(ss, LogDate)*1000)-DATEPART(ms, LogDate), LogDate), 
  [Sum] = COUNT(*)
FROM 
(
    SELECT LogDate = '2013-01-01 00:00:00' UNION ALL
    SELECT LogDate = '2013-01-01 00:00:04' UNION ALL
    SELECT LogDate = '2013-01-01 00:00:06' UNION ALL
    SELECT LogDate = '2013-01-01 00:00:08' UNION ALL
    SELECT LogDate = '2013-01-01 00:00:10'
) a
GROUP BY DATEADD(ms,((DATEPART(ss, LogDate)/5)*5000)-(DATEPART(ss, LogDate)*1000)-DATEPART(ms, LogDate), LogDate)

Upvotes: 2

Related Questions