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

SQL Server Development (2000)

Time intervals

Author

Topic

kevla22
Starting Member

7 Posts

Posted - 2006-03-21 : 11:37:27

Hey guys,

Need some expert advice on tackling time intervals with SQL server.
Basically what i am trying to do is return values between a start and end date (not a problem) but at a given time interval, say every ten minutes.

this is the basic query which works:

INSERT INTO dbo.Questar_Data(DateTime,T,Tagname,DataType, Value)
SELECT DateTime,T,Tagname,DataType, Value
FROM dbo.Insql_Data
WHERE DateTime BETWEEN (SELECT Col001 FROM dbo.Req) AND (SELECT Col002 FROM dbo.Req) AND Datetime = (SELECT Col001 FROM dbo.Req) AND Tagname = (SELECT Taglist FROM dbo.Taglist WHERE [INDEX] = '1')

So it needs to return values that are ten minutes apart between the start and end dates.

Its been a while since i have written SQL and from memory i don't think SQL server has an Interval function (like oracle does), does anyone know how this can be achieved?

Any help would be hugely appreciated.

Cheers

Kevin

ps- using SQL v8

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-21 : 11:52:15

Does this mean that you want you results grouped at 10 minute intervals? If so, you can round a datetime value down to even 10 minute intervals like this:


select [TenMin] = dateadd(minute,(datediff(minute,0,getdate())/10)*10,0)

TenMin                                                 
------------------------------------------------------ 
2006-03-21 11:50:00.000

(1 row(s) affected)

CODO ERGO SUM

kevla22
Starting Member

7 Posts

Posted - 2006-03-21 : 12:07:50

You guys are fast!

What i need to do is return values ten minutes apart, so i am logging data every 5 seconds using industrial SQL and importing a text file that specifies a start and end date and a frequency.

So if the start date was say 10:00:00.00 and a frequency of 10 then i need to return values that have a timestamp of 10:10:00.00, 10:20:00.00, 10:30:00.00, etc up until the end date.

Hope this makes sense! thanks for the speedy response.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-21 : 12:20:02

Hi all,

Kevin - I'm finding it hard to follow exactly what you're asking, but maybe this kind of thing will be useful. Just try running it to see what it does...

declare @StartDate datetime
declare @EndDate datetime
declare @Minutes int

set @StartDate = '10:00:00.000'
set @EndDate   = '17:00:00.000'
set @Minutes   = 10


declare @NumberOfIntervals int
set @NumberOfIntervals = datediff(minute, @StartDate, @EndDate) / @Minutes + 1

set rowcount @NumberOfIntervals

declare @numbers table (i int identity(0, 1), j bit)
insert @numbers select null from master.dbo.syscolumns a, master.dbo.syscolumns b

set rowcount 0

select dateadd(minute, i * @Minutes, @StartDate) from @numbers

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

kevla22
Starting Member

7 Posts

Posted - 2006-03-21 : 12:45:30

Hello Ryan,

I'll try to explain, what you have provided is along the right lines.

I have three tables, a source table (Insql_Data), a destination table (Questar_Data) and a request table (Req).

I want to move data from the Insql_Data to Questar_Data based on the start date, end date and frequency specified in the Req table.

So in the Insql_Data table i have hundreds of logged entries (every 5 seconds coming from a programmable controller) that are values read back from instruments (like a temperature monitor for example). Each entry is logged with a datetime stamp, a unique name (tag) and a value.

I need to copy the data from one table to another based on the interval (frequency) specified in the request file.

Each tag is logged every 5 seconds, so if the interval is 10 and the start time is 10:00:00 i need to grab the data for every ten minutes after, so what the value was at 10:10:00, 10:20:00, 10:30:00 etc.

So this part of my query returns the values between the start and end date for the tag as specified in my taglist table (indexed)

WHERE DateTime BETWEEN (SELECT Startdate FROM dbo.Req) AND (SELECT enddate FROM dbo.Req) AND Datetime = (SELECT startdate FROM dbo.Req) AND Tagname = (SELECT Taglist FROM dbo.Taglist WHERE [INDEX] = '1')

This is fine, but i only want the values at 10 minute intervals.

I hope you can make sense of this! i am going to play around with your suggestion, it seems to do the job (returns time values 10 minutes apart) i just need to merge it with my existing code.

Thanks again Ryan. I appreciate your help, been giving me a headache!

cheers

Kevin

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-03-21 : 13:48:02

No worries Kevin.

Let us know if you manage to get that to work, but if not please post some sample data and the expected output you want - it's always easier to work with examples because we can play with them and have something to test our results against

Ryan Randall
www.monsoonmalabar.com

Ideas are easy. Choosing between them is the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources