How do I generate a series of hourly averages in MySQL?

Question

I've got data in ten minutes intervals in my table:

2009-01-26 00:00:00      12
2009-01-26 00:10:00      1.1
2009-01-26 00:20:00      11
2009-01-26 00:30:00      0
2009-01-26 00:40:00      5
2009-01-26 00:50:00      3.4
2009-01-26 01:00:00      7
2009-01-26 01:10:00      7
2009-01-26 01:20:00      7.2
2009-01-26 01:30:00      3
2009-01-26 01:40:00      25
2009-01-26 01:50:00      4
2009-01-26 02:00:00      3
2009-01-26 02:10:00      4
etc.

Is it possible to formulate a single SQL-query for MySQL which will return a series of averages over each hour?

In this case it should return:

5.42
8.87
etc.

Answer 1

This should work:

SELECT AVG( value ) , thetime
FROM hourly_averages
GROUP BY HOUR( thetime )

Here's the result

AVG(value)          thetime
5.4166666865349     2009-01-26 00:00:00
8.8666666348775     2009-01-26 01:00:00
3.5                 2009-01-26 02:00:00

Answer 2

It's unclear whether you want the average to be aggregated over days or not.

If you want a different average for midnight on the 26th vs midnight on the 27th, then modify Mabwi's query thus:

SELECT AVG( value ) , thetime
FROM hourly_averages
GROUP BY DATE( thetime ), HOUR( thetime )

Note the additional DATE() in the GROUP BY clause. Without this, the query would average together all of the data from 00:00 to 00:59 without regard to the date on which it happened.