Friday, December 10, 2010

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