TSQL Group by N Seconds

Question

I can group by seconds or minutes with something like the following:

SELECT datepart(minute, Time)
      ,count(*) as hits
FROM Log
GROUP BY datepart(minute, Time)

Is there a way I can do the same thing, but with a specified amount of seconds, so group by "every 10 seconds" for example?

More info:
This is combined with a Where Time between as well:

SELECT datepart(minute, Time)
      ,count(*) as hits
FROM Log with (nolock)
WHERE Time between dateadd(minute, -2, getdate()) and getdate()
GROUP BY datepart(minute, Time)

manji · Accepted Answer · 2011-04-01T12:47:35.343

6

Try this:

DATEDIFF (ss ,'19700101' ,Time )) will give the amount of seconds since 01/01/1970 (you can choose another date that is less than all your dates). Divide it by ten to have a GROUP BY by each 10 seconds:

SELECT DATEDIFF (ss ,'19700101' ,Time )/10, count(*) as hits
  FROM Log
 GROUP BY DATEDIFF (ss ,'19700101' ,Time )/10

(with time):

SELECT convert(varchar, min(Time), 108) time,
       DATEDIFF (ss ,'19700101' ,Time )/10 sec_interval,
       count(*) as hits
  FROM Log
 GROUP BY DATEDIFF (ss ,'19700101' ,Time )/10

edited Apr 01 '11 at 12:47

answered Apr 01 '11 at 12:20

manji

47,442
5
96
103

This seems to work , is there any way I can output a formated time (maybe min:second)? – Kyle Brandt Apr 01 '11 at 12:32
@Manji: Might work, end goal is passing the data to flot for graphing -- so need to get a better handle on flot I think. Right now I have it graphing the first query you gave me though. – Kyle Brandt Apr 01 '11 at 12:58
@Manji: So it goes SQL -> Python/Django -> Javascript/jQuery. So perhaps better to handle the time conversion in python anyways. – Kyle Brandt Apr 01 '11 at 13:05
@Manji: Unix time is pretty much perfect! Flot uses JS timestamps which are just milliseconds since 1970, so I just multiply this result by 1000 in the JS code. – Kyle Brandt Apr 01 '11 at 13:30
@Manji: Hmm.. actually this is odd. I do get 12 results for 2 minutes, but the sec interval column shows it incrementing by 1 second each time. I guess I still wasn't clear :-) Looking for 10 second chunks that would be spaced 10 seconds apart. Perhaps I am lost in the time conversions... – Kyle Brandt Apr 01 '11 at 13:35
the diff in seconds is divided by 10 that's why it's icremented by 1 in the select. Use `(DATEDIFF (ss ,'19700101' ,ttime )/10)*10 sec_interval` you wil see real seconds diff (incremented by 10 this time) – manji Apr 01 '11 at 13:43

TSQL Group by N Seconds

1 Answers1

Linked