SQL average hours

Question

I have an SQL table with two fields: TimeStamp and Value. The following is a snippet of some 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 accept the hourly average of the Value column, however I cannot get this to work correctly. The end result will show the starting hour for the TimeStamp and the average value for the Value column.

For the final output, I want to get the full timestamp as a result, not just an hour. So, from 14:00 to 14:59 in 2005-02-17 the resulting result will be:

 2005-02-17 14:00:00 2.125 

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

Fiddle example.

Answer 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 +

Answer 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, the DATEPART function calculates the hour value for all values ​​in the DATETIME column and, based on each hour, the average value of the second column at the hour level is calculated.

Answer 4

I think you also want it to be grouped by date, not just by hour, right?

 select convert(VARCHAR(10), date, 111) as aDate, datepart(HH, date) anHour, avg(value) anAverage from t group by convert(VARCHAR(10), date, 111), datepart(HH, date) 

Or that:

 ; with aTable as ( select convert(VARCHAR(10), date, 111) as aDate, datepart(HH, date) anHour, value from t) select aDate, anHour, avg(value) from aTable group by aDate, anHour 

Answer 5

 SELECT AVG(myvalue) [Average], DATEADD(HOUR, DATEPART(HOUR, mydate), CAST(CAST(mydate as Date) as datetime)) [Hour] FROM myTable GROUP BY DATEADD(HOUR, DATEPART(HOUR, mydate), CAST(CAST(mydate as Date) as datetime)) ORDER BY DATEADD(HOUR, DATEPART(HOUR, mydate), CAST(CAST(mydate as Date) as datetime))