CODEWITHSUNDEEP
sql-servert-sqldatetime
James Anderson
James Anderson

Reputation: 13

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

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))

Upvotes: 1

Views: 745

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522636

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));

Upvotes: 1

Related Questions