How can I group by arbitary time period with SQL

Question

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

I removed the Delphi related tags, because your question has absolutely no content related to Delphi. It's strictly a SQL Server question. Please only add tags that are actually applicable to the question being asked. Thanks. — Ken White, Jan 17 '13 at 17:54
How will you decide which five minute or how will u decide 1 hour? can it be like from 1:10 am to 2:10 am like this or it will be always like 1 am to 2 am like that.. — Gulli Meel, Jan 17 '13 at 17:59
It is enough with 1 am to 2am. And I would like to present them as chart columns in a diagram in a Delphi application. — Roland Bengtsson, Jan 17 '13 at 21:42

rickythefox · Answer 1 · 2013-01-17T18:07:16.710

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)

How can I group by arbitary time period with SQL

1 Answers1