Take Hourly Average in SQL

Question

I have a SQL table with 2 fields: TimeStamp and Value. Below is an excerpt of some of the data.

2005-02-17 13:31:00     2
2005-02-17 13:46:00     3
2005-02-17 14:01:00     1.7
2005-02-17 14:16:00     2.3
2005-02-17 14:31:00     2
2005-02-17 14:46:00     2.5
2005-02-17 15:01:00     2.2
2005-02-17 15:16:00     2.4
2005-02-17 15:31:00     2.6
2005-02-17 15:46:00     2.6
2005-02-17 16:01:00     2.7

I am trying to take an hourly average of the Value column, however I cannot seem to make this work correctly. The final output would show the starting hour for the TimeStamp, and the averaged value for the Value column.

For the final output I am looking to get a full timestamp as a result, not just the hour. So from 14:00 - 14:59 on 2005-02-17 the resulting output would be:

2005-02-17 14:00:00    2.125

Solution 1

I would do it like this:

SELECT      CAST(FLOOR(CAST(timestamp AS float)) AS datetime) AS day --strip time
            , DATEPART(hh, timestamp) AS hour
            , AVG(value) AS average
FROM        times
GROUP BY    CAST(FLOOR(CAST(timestamp AS float)) AS datetime)
            , DATEPART(hh, timestamp)

Example fiddle.

Solution 2

select Time_Stamp_Hour=dateadd(hh,datepart(hh,Time_Stamp), cast(CAST(Time_Stamp as date) as datetime))
, AvgValue=AVG(Value)
from ValueLog
group by dateadd(hh,datepart(hh,Time_Stamp), cast(CAST(Time_Stamp as date) as datetime))

Result:

Time_Stamp_Hour         AvgValue
----------------------- ----------------------
2005-02-17 13:00:00.000 2.5
2005-02-17 14:00:00.000 2.125
2005-02-17 15:00:00.000 2.45
2005-02-17 16:00:00.000 2.7

Compatibility: Sql Server 2008+

Solution 3

SELECT DATEPART(hour,Col1) as hourcol,AVG(Col2)
FROM Yourtable
GROUP BY hourcol;

OR

SELECT SUBSTRING(Col1,1,14)+'00' AS hourcol,AVG(Col2)
FROM Yourtable
GROUP BY hourcol;

In this query DATEPART function calculates the hour value for all the values in the DATETIME column and based on each hour the average of 2nd column is calculated at hour level.