SQL Retrieve historical sensor values at specific date times

Question

Hello, I have a database that has a vessel pressure that is trended every 30 seconds. I would like to be able to retrieve a recording at a specific time of day for a number of days in the past. I am not able to base this on the server as a Stored Procedure or a View – I only have read access. I have created a routine that works for 17 days in the past but I would like it to be able to give a few months data. I could I guess just make repeated calls from the c# app, but that seems even more inefficient than the method I am using now. Does anyone have any recommendations.

Brad

declare @DeadBand DateTime
declare @EndDate DateTime
declare @End varchar(50)
declare @Start varchar(50)
declare @Temp varchar(50)
declare @NumberDays int
declare @dec int

declare @InnerSqlQry as varchar(8000)
declare @SqlQry as varchar(8000)
SET @NumberDays = 5
SET @dec = @NumberDays
SET @Start = CONVERT(Varchar(30),dateadd(dd,-@NumberDays,GetDate()))
SET @End = CONVERT(Varchar(30),GetDate())
SET @DeadBand= + CONVERT(Varchar(30),dateadd(ss,90,@Start))


   SET @Width = + CONVERT(Varchar(30),dateadd(ss,90,@Start))
WHILE (@dec >= 1)
    BEGIN



                set @InnerSqlQry = ' Select DateTime,ACMKWHYTD_1 From WideHistory        Where  Datetime >='''+convert(varchar(30), @Start ,120) +''' AND Datetime <= '''+convert(varchar(30),@DeadBand,120) +''' and wwResolution =60000 and wwRetrievalMode = "cyclic" '

            if(@dec = 1)
                    begin
                            set @SqlQry = @SqlQry+ N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' )'
                    end

            if((@dec > 1) and (@dec < @NumberDays))
                    begin
                            set @SqlQry = @SqlQry+ N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) union '
                    end

            if(@dec = @NumberDays)
                    begin
                            set @SqlQry = N' Select top 1 * from openquery(INSQL,''' + REPLACE(@InnerSqlQry, '''', '''''') + ''' ) union '
                    end

            set @dec = @dec - 1

            SET @Start = CONVERT(Varchar(30),dateadd(dd,-@dec,GetDate()))
            SET @DeadBand= + CONVERT(Varchar(30),dateadd(ss,90,@Start))

    END
    print(@sqlQry)
    exec(@sqlQry)

Answer 1

Couldn't you do this more efficiently by converting the datetime into a unixtime number and modding it to give you the time of day.

The where clause would filter it for the date range you want and then the mod trick would discard all records that were not at the time of day you wanted.

Then you can query it once rather than once for each day.

Answer 2

Don't use a loop...and I don't think you have too. Did you look at DatePart?

This query for example would get every record between a start and stop date that occured at 12:30.

Select DateTime,ACMKWHYTD_1 From WideHistory  
WHERE DateTime BETWEEN @Start AND @DeadEnd
AND DATEPART(hh,datetime) =12 AND DatePart(mi,datetime)=30

Alternativley you can zero out the date and then use a datediff function to compare the number of minutes between your target time and what your looking for example.

where ABS(datediff(mi,convert(nvarchar(10),getdate(),8),'10:00PM'))=60 This will get you all records that occured within 60 minutes of 10:00PM so from 9-11 PM

Answer 3

I've seen quite a few tricks for converting a DATETIME into "just a time". Infact where I work there was quite some time spent finding the fastest. What people seem to forget is that "shortest code" is not always fastest, and often includes Implicit type conversions hidden away. The fastest we came up with is...

DATEADD(DAY, DATEDIFF(MINUTE, 0, ), 0)

The reason I mention it is that once you have the time component, everything becomes easier and allows for easier code changes for subtly different needs.

DECLARE
    @start_date    DATETIME,
    @end_date      DATETIME,
    @chosen_time   DATETIME
SELECT
    @start_date    = '2009 Jan 05 00:00',
    @end_date      = '2009 Jan 12 00:00',
    @chosen_time   = '1900 Jan 01 17:30'   -- '1900 Jan 01' is day 0

SELECT
    <whatever>
FROM
    WideHistory  
WHERE
        [WideHistory].DateTime >= @start_date
    AND [WideHistory].DateTime <  @end_date
    AND DATEADD(DAY, DATEDIFF(MINUTE, 0, [WideHistory].DateTime), 0) = @chosen_time

One could easily adapt that to several key times of day, or a range of times, etc, etc...

EDIT:

As mentioned in comments to this answer, a change to the database structure would also benefit here. Having a DATE field as well as a TIME field. Often superfluous, but in this case could be beneficial.