Home Select data using getdate for previous day - but only return data for the previous day not previous 24 hours
Reply: 1

Select data using getdate for previous day - but only return data for the previous day not previous 24 hours

James Anderson
1#
James Anderson Published in 2018-01-11 05:00:40Z

I want to run a query that will display an average temperature value for every hour of the previous day displayed from midnight onwards.

The Table has column UpdateOn with timestamp in format 2018-01-10 13:56:06.000 and column P1Temp which has temperature data. The data is entered in the table approx every minute.

I have the following SQL query that displays data but only for the previous 24 hours from when the command is run. This can splits the data over two calendar days depending when the command is run, when I want it just to return every hour of just the previous day regardless of when the command is run.

select UpdateOn=dateadd(hh,datepart(hh,UpdateOn), cast(CAST(UpdateOn as date) as datetime)), round(AVG(P1Temp), 1)

from TABLE

where UpdateOn between dateadd(day,-1,getdate()) and getdate()

group by dateadd(hh,datepart(hh,UpdateOn), cast(CAST(UpdateOn as date) as datetime))

Result (splits over two calendar days but returns 25 results (24 hours))
Tim Biegeleisen
2#
Tim Biegeleisen Reply to 2018-01-11 05:23:57Z

From the MSDN documentation, here is the expression for today at midnight:

DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))

If I read your question correctly, you want to target the 24 hours starting yesterday at midnight up to, but not including, today at midnight. The query below is almost identical to what you already have, except that the WHERE clause imposes the range you want.

SELECT
    UpdateOn = DATEADD(hh, DATEPART(hh, UpdateOn),
        CAST(CAST(UpdateOn AS DATE) AS DATETIME)),
    ROUND(AVG(P1Temp), 1)
FROM TABLE
WHERE
    UpdateOn >=
        DATEADD(day, -1, DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))) AND
    UpdateOn < DATEADD(Day, 0, DATEDIFF(Day, 0, GetDate()))
GROUP BY
    DATEADD(hh, DATEPART(hh, UpdateOn),
        CAST(CAST(UpdateOn AS DATE) AS DATETIME));
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300782 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO