How to get data date / time range

Hello

My first post so please be peasant :o). My problem is that i dont know how to solve following problem, and after searching on the net for several hours I give up. :o(

I have a table that contains data that is stored with 30 sec interval over a week.
 
But now I want to make query where I can set some parameter like start date and stop date and time interval. So I want to se the data over a date period an time period and with a interval like 2 min not the 30 sec the data I stored.

Please helpOrginal
Value      Date            Time
76.9      2009-04-19      01:34:33.0000000
76      2009-04-19      01:34:41.0000000
7      2009-04-19      01:35:02.0000000
87      2009-04-19      01:39:36.0000000
87.9      2009-04-19      01:39:53.0000000
66      2009-04-19      01:40:54.0000000
0      2009-04-19      01:42:58.0000000

after
Value      Date            Time
76.9      2009-04-19      01:35:00.0000000
76      2009-04-19      01:37:00.0000000
7      2009-04-19      01:39:00.0000000
87      2009-04-19      01:41:00.0000000
87.9      2009-04-19      01:43:00.0000000
66      2009-04-19      01:45:00.0000000
0      2009-04-19      01:47:00.0000000


Declare @Interval int
Declare @StartDate date
Declare @StopDate date
Declare @StartTime time
 
 
Set @StartDate = convert (varchar(10), getdate()-1, 101)
Set @StartDate = convert (varchar(10), getdate(), 101)
Set @StartTime convert (varchar(10), getdate(), 108)
Set @Interval = 2 -- 2 min
 
 
 
CREATE TABLE [dbo].[TagHis](
	[Value] [float] NULL,
	[Date] [date] NOT NULL,
	[Time] [time](7) NOT NULL,

Open in new window

JesperSTDKAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
interesting...
select *
  from TagHis
 where Date >= @startdate
   and date <= @enddate
   and time >= @starttime
   and time <= @endtime
   and datepart(second,time) = 0
   and datepart(minute,time) % 2 = 0    

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
can you please clarify the "rounding" schema for the time?
aka, as from which "minute+second" it has to be rounded to which minute.

0
 
JesperSTDKAuthor Commented:
Im not sure what you mean but, I want to make a report over the last day production
So if I start the query at time 06:00:0 date 20-04-09 the query return data from
time 06:00:0 date 19-04-09 until time 06:00:0 date 20-04-09

And all my data in the table is stored every  30 sec but in the report I only want to see the data from every 2 min, so
Before
Value      Date      Time      show this
1      19-04-009      01:35:30.0      
2      19-04-009      01:36:00.0      x
3      19-04-009      01:36:30.0      
4      19-04-009      01:37:00.0      
5      19-04-009      01:37:30.0      
6      19-04-009      01:38:00.0      X
After
Value      Date      Time      show this
2      19-04-009      01:36:00.0      x
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean:
select *
  from TagHis
 where Date >= @startdate
   and date <= @enddate
   and time >= @starttime
   and time <= @endtime
   and second(time) = 0
   and minute(time) % 2 = 0     

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
site note: don't use float. use decimal instead, please. float is unprecise data type, and should only be used where really needed.
0
 
JesperSTDKAuthor Commented:
I get a error
with the code The function  and second(time) = 0   and minute(time) % 2 = 0 is not a recognized built-in function name.
0
 
JesperSTDKAuthor Commented:
Tanks is works great just one problem with the time, if I want to read data from the time period
Start at
19:04:09 date and 06:06:00 time
Stop at
20:04:09 date and 06:06:00 time to

I dont get the data from the night at 20:04:06 date time frame 00:00:00 to 06:06:00 do you have an idea

Tanks Jesper
0
 
JesperSTDKAuthor Commented:
Use ProdDB
go
Declare @Interval      int
Declare @StartDate      date
Declare @enddate      date
Declare @StartTime      time
Declare @endTime      time
 
Set @StartDate      = convert (varchar(10), getdate()-1, 101)
Set @enddate      = convert (varchar(10), getdate(), 101)
Set @StartTime      = convert (varchar(10), getdate(), 108)
Set @endTime      = convert (varchar(10), getdate(), 108)
Set @Interval      = 1

select *
from TagLog
 
      where            datepart(minute,logtime) % @Interval = 0    
                  and datepart(second,LogTime) = 0
                  and (LogDate >= @StartDate and LogTime >= '06:00:00.0000000')
                  and LogDate <= @enddate

My final code Tanks to angelIII
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see
select *
  from TagHis
 where Date >= @startdate
   and date <= @enddate
   and ( time >= @starttime or Date > @startdate )
   and ( time <= @endtime or date < @enddate )
   and datepart(second,time) = 0
   and datepart(minute,time) % 2 = 0  

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.