?
Solved

How to get data date / time range

Posted on 2009-04-20
9
Medium Priority
?
697 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:JesperSTDK
  • 5
  • 4
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24184461
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
 

Author Comment

by:JesperSTDK
ID: 24184780
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24184822
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24184829
site note: don't use float. use decimal instead, please. float is unprecise data type, and should only be used where really needed.
0
 

Author Comment

by:JesperSTDK
ID: 24185208
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1000 total points
ID: 24185229
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
 

Author Comment

by:JesperSTDK
ID: 24185683
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
 

Author Comment

by:JesperSTDK
ID: 24185752
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24185775
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

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question