SQL: Group datetime by an arbitrary time interval

Yesterday, a co-worker came to me with a puzzle, he wanted to write a query that would group records by intervals of 5, 10, 15 minutes, ideally just passing a parameter to the query to specify the time interval, I sat down and after 5 minutes I came up with the solution:

declare @interval int

set @interval = 5

select datepart(hh, DateTimeColumn)

, datepart(mi, DateTimeColumn)/@interval*@interval

, count(*)

from thetable

group by datepart(hh, DateTimeColumn)

, datepart(mi, DateTimeColumn)/@interval*@interval

The trick is highlighted, though it looks like the division and multiplication eliminate each other, what is really happening is an integer division, which, multiplied by the same number, gives you the right intervals:

here's the (minutes) values pattern for an interval of 5


minute divided by 5 multiplied by 5
0 0 0
1 0 0
...
5 1 5
6 1 5
...
10 2 10
11 2 10
...
from there you can include more fields in your select criteria, make it prettier, etc

declare @interval int

set @interval = 5

select convert(varchar(8), DTColumn, 1)+' '

      +convert(varchar(2), datepart(hh, DTColumn))+':'

      +convert(varchar(2), datepart(mi, DTColumn)/@interval*@interval)

, count(*)

from the_table

group by convert(varchar(8), DTColumn, 1)+' '

      +convert(varchar(2), datepart(hh, DTColumn))+':'

      +convert(varchar(2), datepart(mi, DTColumn)/@interval*@interval)

which produces something like:

11/12/10 10:10    28

11/12/10 10:15    11

11/12/10 10:20    57

hope this is useful