MySQL: hourly average value

Question

Hello,

I have a table with a 'timestamp' column and a 'value' column where the values are roughly 3 seconds apart.

I'm trying to return a table that has daily average values.

So, something like this is what i'm looking for.

| timestamp  | average |

| 2010-06-02 |  456.6  |

| 2010-06-03 |  589.4  |

| 2010-06-04 |  268.5  |

etc...

Any help on this would be greatly appreciated.

Answer 1

This assumes that your timestamp column only contains information about the day, but not the time. That way, the dates can be grouped together:

select timestamp, AVG(value) as average
from TABLE_NAME
group by timestamp

Answer 2

SELECT DATE(timestamp), AVG(value)
FROM table
GROUP BY DATE(timestamp)

Since you want the day instead of each timestamp

Answer 3

 select DATE(timestamp), AVG(value)
 from TABLE
 group by DATE(timestamp)