TSQL Group by N Seconds

问题:

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)

回答1:

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