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

@dldnh this is sql server. click the tag ([tag:tsql]) to get the idea — Shiplu Mokaddim, Mar 16 '12 at 20:41

score 6 · Accepted Answer · answered Mar 17 '12 at 03:55

6

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.

answered Mar 17 '12 at 03:55

pete

24,141
4
37
51

1

This works great. Is there anyway to put the hour back into the timestamp? – Andrew Mar 19 '12 at 16:44

score 5 · Answer 2 · answered Mar 17 '12 at 03:36

5

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+

answered Mar 17 '12 at 03:36

John Dewey

6,985
3
22
26

Great, the double cast does the trick. Before I've had separate columns for datepart hour and date. You can still simpy `group by CAST(Time_Stamp as date), datepart(hh,Time_Stamp)` (and not the whole dateadd function). – Tomasz Gandor Jan 15 '20 at 13:13

Teja · Answer 3 · 2012-03-19T13:26:54.853

4

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.

edited Mar 19 '12 at 13:26

answered Mar 16 '12 at 20:38

Teja

13,214
36
93
155

This works, however in the GROUP BY clause I want to group by the date and hour. So, for the interval of 2005-02-17 14:00 through 2005-02-17 14:59 I would like the TimeStamp column to show 2005-02-17 14:00 instead of just 14. – Andrew Mar 16 '12 at 20:56
Okay .. if that's the case try using both substring and concat functions together to get the desired output... try it out n tell me... if u don't get it I will help u with the answer ... – Teja Mar 16 '12 at 22:12
This takes only the "hour", but he needs date and hout together. For example: SELECT DATEPART(hour,'2012-03-19 09:12') results "9" but he needs "2012-03-19 09:00" – adyusuf Mar 19 '12 at 07:13
1

@Hooijdonk Yes I know... But I asked him to give a try ... I know whats the solution is.... – Teja Mar 19 '12 at 13:17

score 0 · Answer 4 · answered Mar 16 '12 at 20:46

I think you also want it grouped by date, not only 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 this:

; 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

score 0 · Answer 5 · answered Mar 16 '12 at 20:48

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

Take Hourly Average in SQL

5 Answers5

Linked