Group by Time Interval - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Group by Time Interval

Author

Topic

raisor
Starting Member

28 Posts

Posted - 2008-03-05 : 15:40:16

Hello,

I'm trying to create a group by a set (but can change it later on) time interval.
What I mean by this is the following. I would like to group my data by 5 second intervals, and perhaps have a count for it as well - but that's not required.

For example I have the following data:

PRODUCT, DOOR, TIME_LOCATED
-------------------------------------
chips, 1, 3/5/2008 12:33:30 PM
harddisk, 1, 3/5/2008 12:33:30 PM
tea, 1, 3/5/2008 12:33:31 PM
software, 1, 3/5/2008 12:33:31 PM
chips, 1, 3/5/2008 12:33:32 PM
chips, 1, 3/5/2008 12:33:33 PM
chips, 2, 3/5/2008 12:33:34 PM
software, 1, 3/5/2008 12:33:40 PM
tea, 1, 3/5/2008 12:33:40 PM
software, 1, 3/5/2008 12:33:41 PM

Result in 5 second intervals (so group by values between 3/5/2008 12:33:30 and 3/5/2008 12:33:35)

Something like this?, or does have somebody a better idea?

PRODUCT, DOOR, TIME_LOCATED, COUNT
-------------------------------------------
harddisk, 1, 3/5/2008 12:33:30 PM 1
tea, 1, 3/5/2008 12:33:31 PM 1
software, 1, 3/5/2008 12:33:31 PM 1
chips, 1, 3/5/2008 12:33:33 PM 3
chips, 2, 3/5/2008 12:33:34 PM 1
software, 1, 3/5/2008 12:33:40 PM 2
tea, 1, 3/5/2008 12:33:40 PM 1

THANKS!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-05 : 15:48:14

Start of Time Period Functions:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64755


select
	Round_down_to_5_Minute =
	dateadd(mi,(datepart(mi,TIME_LOCATED)/5)*5,dateadd(hh,datediff(hh,0,TIME_LOCATED),0))

from
	(
	-- Test Date
	select TIME_LOCATED = convert(datetime,'20080305 12:33:32.997') union all
	select TIME_LOCATED = '20080305 12:35:32.997'
	) a

Results:

Round_down_to_5_Minute                                 
------------------------------------------------------ 
2008-03-05 12:30:00.000
2008-03-05 12:35:00.000

(2 row(s) affected)

CODO ERGO SUM

raisor
Starting Member

28 Posts

Posted - 2008-03-05 : 16:50:08

Unfortunately that won't work, it will give an overflow when setting it to seconds.

Thanks for your post!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-05 : 17:39:48

I mis-read your post, I thought you wanted to round to 5 minutes, not 5 seconds.

select
	TIME_LOCATED,
	Round_down_to_5_Second =
	dateadd(ms,((datepart(ss,TIME_LOCATED)/5)*5000)-(datepart(ss,TIME_LOCATED)*1000)-datepart(ms,TIME_LOCATED),TIME_LOCATED)
from
	(
	-- Test Date
	select TIME_LOCATED = convert(datetime,'20080305 12:33:32.997') union all
	select TIME_LOCATED = '20080305 12:35:32.997' union all
	select TIME_LOCATED = '20080305 12:35:39.997'
	) a

Results:
TIME_LOCATED                                           Round_down_to_5_Second                                 
------------------------------------------------------ ------------------------------------------------------ 
2008-03-05 12:33:32.997                                2008-03-05 12:33:30.000
2008-03-05 12:35:32.997                                2008-03-05 12:35:30.000
2008-03-05 12:35:39.997                                2008-03-05 12:35:35.000

(3 row(s) affected)

CODO ERGO SUM

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-05 : 22:44:32

Is that what you wanted?

--Jeff Moden

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-03-06 : 09:45:55

A different way to do it:

select
	TIME_LOCATED,
	Round_down_to_5_Second =
	dateadd(ms,-((datepart(ss,TIME_LOCATED)*1000)+datepart(ms,TIME_LOCATED))%5000,TIME_LOCATED)
from
	(
	-- Test Date
	select TIME_LOCATED = convert(datetime,'20080305 12:33:32.997') union all
	select TIME_LOCATED = '20080305 12:35:32.997' union all
	select TIME_LOCATED = '20080305 12:35:32.997' union all
	select TIME_LOCATED = '20080305 12:40:00.000' union all
	select TIME_LOCATED = '20080305 12:40:00.003' union all
	select TIME_LOCATED = '20080305 12:40:44.997' union all
	select TIME_LOCATED = '20080305 12:40:45.000'
	) a


Results:
TIME_LOCATED               Round_down_to_5_Second
-----------------------    -----------------------
2008-03-05 12:33:32.997    2008-03-05 12:33:30.000
2008-03-05 12:35:32.997    2008-03-05 12:35:30.000
2008-03-05 12:35:32.997    2008-03-05 12:35:30.000
2008-03-05 12:40:00.000    2008-03-05 12:40:00.000
2008-03-05 12:40:00.003    2008-03-05 12:40:00.000
2008-03-05 12:40:44.997    2008-03-05 12:40:40.000
2008-03-05 12:40:45.000    2008-03-05 12:40:45.000

(7 row(s) affected)

CODO ERGO SUM

raisor
Starting Member

28 Posts

Posted - 2008-03-09 : 11:54:08

Guys, my apologies that I havent' responded any sooner!

I got caught up on another project and hadn't had any time too look it over again.

Actually YES!, that is exactly what I'm looking for!, so thank you very much.
I'm working on another query that I might have a question for, but will post that at another time.

Thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources