Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Take Hourly Average in SQL

Tags:

tsql

ssms

average

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
like image 738
Andrew Avatar asked Mar 16 '12 20:03

Andrew


2 Answers

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.

like image 113
pete Avatar answered Nov 12 '22 10:11

pete


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+

like image 25
John Dewey Avatar answered Nov 12 '22 11:11

John Dewey