Select Query to group records by 5 minutes

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 2000 Forums

Transact-SQL (2000)

Select Query to group records by 5 minutes

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-11-07 : 07:37:43

Sreejith writes "Hi

I have a table with following fields - unixtime,rate
I want to display average,max,min rates, by grouping records by say 10 minutes, 30 minutes, 1 hr,etc..

Please tell me how i can group records by time - i can convert unixtime to normal datetime.

Expecting your reply

thanks

sreejith"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-11-07 : 15:10:22

Create a table of sequential number in your database, beginning with zero and going as high as necessary.
Use this table in conjunction with a startdate and the dateadd() function to generate a resultset of intervals in whatever increment you desire.
Use this resultset as your base, left outer join to your data, linking to the interval set with the BETWEEN operator.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-07 : 15:45:41

For us to help you we could use some more info.

Read the hint link listed below and post the additional info needed.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-11-07 : 16:13:47

Here is one way to group by a specified number of minute intervals
(if I understand the question):


declare @intervalMinutes int
set @intervalMinutes = 5

select	intervals
from	(
	select	dateadd(minute, datediff(minute, 0, dt) - 
			(datediff(minute, 0, dt)%@intervalMinutes)
			, 0) as intervals
	from	(
		select getdate() dt union all
		select dateadd(minute, 3, getdate()) union all
		select dateadd(minute, 6, getdate()) union all
		select dateadd(minute, 9, getdate()) union all
		select dateadd(minute, 12, getdate()) union all
		select dateadd(minute, 15, getdate()) union all
		select dateadd(minute, 17, getdate()) union all
		select dateadd(minute, 20, getdate()) union all
		select dateadd(minute, 23, getdate()) union all
		select dateadd(minute, 26, getdate()) union all
		select dateadd(minute, 29, getdate()) union all
		select dateadd(minute, 32, getdate())) a
	) a
group by intervals

Be One with the Optimizer
TG

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-07 : 19:16:12

This shows how you can round off a datetime to 5, 10, 15, 20, and 30 minute intervals. You can group by those times after rounding them off. I'm sure you can see from this code how to round off to other minute intervals.


select 
	[5min]  = dateadd(minute,(datediff(minute,0,a.dt)/5)*5,0),
	[10min] = dateadd(minute,(datediff(minute,0,a.dt)/10)*10,0),
	[15min] = dateadd(minute,(datediff(minute,0,a.dt)/15)*15,0),
	[20min] = dateadd(minute,(datediff(minute,0,a.dt)/20)*20,0),
	[30min] = dateadd(minute,(datediff(minute,0,a.dt)/30)*30,0),
	a.dt
from
	(select dt = convert(datetime,'2005-11-07 19:55:19.757') ) a

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources