MS SQL Query to count records within 5 minute intervals

This will show you how you can round time down to 5 minute increments:

declare @DT datetime
set @dt=getdate()
select dateadd(n, (datediff(n, 0,@dt)/5)*5,0),@dt
set @dt=dateadd(n,1,getdate())
select dateadd(n, (datediff(n, 0,@dt)/5)*5,0),@dt
set @dt=dateadd(n,4,getdate())
select dateadd(n, (datediff(n, 0,@dt)/5)*5,0),@dt
set @dt=dateadd(n,7,getdate())
select dateadd(n, (datediff(n, 0,@dt)/5)*5,0),@dt

Select allOpen in new window

Select  DateDiff(m, '1/1/2000', MyDatefield) / 5, count(DateDiff(m, '1/1/2000', MyDatefield) / 5)
from MyTable
Group by DateDiff(m, '1/1/2000', MyDatefield) / 5

DW: m is for month :)

mi or n is for minute

DanielWilson,

Sorry for my ignorance but how would I name the columns so I can do an order by?

I'm guessing the number in the first column is the time relative to 1900 or whatever?  How can I convert that back to the actual date/time?

The one that I have does that.  It gives you the time.

Brandon,

This is all I get with yours.
Untitled.png

That was just an example of how it would round various times (the right column) down to the 5 minute increment.

This one is very close to what I'm after.

     select dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0), count(*)
     from YourTable
     group by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)
     order by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)

It is only displaying the time periods where a record exists.  How can I get it to display the period even if it is zero (IE has no entry)?

Are you on SQL 2000 or 2005?

SQL 2000.  I'm using SQL Server 2005 Management Studio.

In order to do that, you need to have a time coordinate table.  We can fake that though if you have a numbers table.  If you have neither, we would have to generate numbers in the procedure and it would not be efficient.  Do you have a numbers table?

ie. a table that contains sequential numbers.

I do have a numbers table.  Currently it only goes to 9000.

I think what we have will work for my purpose.  If you have time additional info on getting the 0 items would be nice but not necessary.

Thank you both for your (quick) help!

Thank you both very much!

You'll need more than 9000.  Basically you'll need n numbers where n is the number of 5 minute increments you will have from your earliest to your latest data point.

The basic principal is.

declare @minD datetime
@maxD datetime
select @mind = dateadd(n, (datediff(n, 0,min(YourDateColumn))/5)*5,0)
, @maxd = dateadd(n, (datediff(n, 0,max(YourDateColumn))/5)*5,0)
 
--To see the approximate numbers you need as of NOW, not counting the 288 increments you will need per day.
 
select datediff(d,@mind,@max)*288
 
 
 
--Your list of ALL dates would then be
 
select dateadd(n, (n-1)*5,@minD)
from YourNumbersTable
where dateadd(n, (n-1)*5,@minD) <= @MaxD
 
--and incorporated into your above query.  I'm using inc to refer to an increment (of 5).
 
select allinc.theInc,isnull(cnt,0) as Count
(select dateadd(n, (n-1)*5,@minD) theInc
from YourNumbersTable
where dateadd(n, (n-1)*5,@minD) <= @MaxD) as AllInc
left outer join 
(select dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0) theinc, count(*) cnt
from YourTable
group by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)
order by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)) ThisInc
on allinc.theinc = thisinc.theinc

Select allOpen in new window