Averaging Data by Date

[bergy]

Background:

We are collecting data and pushing it to MySQL at various intervals. Sometimes it may be every minute, other times every 15 minutes, and all ranges in between. This data is timestamped using the UNIX epoch timestamp (if needed I could convert this to a MySQL timestamp).

When viewing the data in just one day, it is okay to show all rows, but we run into a problem with load times when we go past say a week... (at 1440 rows per day a week contains 10,080 rows - on top of that if we're looking at multiple columns say 3, we're now at 30,000+ pieces of data). This is really bogging down our graph displays.

Question:

I'm wondering if there is way to say find the average of rows between X period, so if we're looking at an entire year, I can average and sum the minutely data to hourly or daily data. I know there is an AVG function, but the way I understand it is that that would average all given rows, I want to be able to average all rows between X and Y have that as a returned row from the query and then continue to average between the next X and Y. I hope that makes sense.

I guess one way to attack it is multiple queries, but I'd like to be able to do this in a single query if it's possible. If we're looking at daily averages over a year it would be nice to not have to submit 365 separate queries to the database - not to mention the math/logistics in figuring out X date starts at Y timestamp and ends at Z timestamp.

The idea is to make this query code/platform independent so whether we're accessing data via PHP or Java we don't have to write separate logic on each platform to decide how the queries are going to look.

Any ideas on attacking this problem would be welcomed - Thanks a bunch!

[Atli]

Hi.

You can use the date functions and the GROUP BY clause to group the rows into days or weeks or whatever you need, and return an AVG just for that period.

For example:
[code=mysql]SELECT
AVG(`Value`) AS `Average`,
YEAR(`Date`) AS `Year`,
MONTHNAME(`Date `) AS `Month`
FROM `random`
GROUP BY `Year`, `Month`;[/code]
Would give you and average for each month in that table.
Like:

+---------+------+-----------+
| Average | Year | Month     |
+---------+------+-----------+
| 50.5726 | 2007 | April     | 
| 50.4852 | 2007 | August    | 
| 49.9294 | 2007 | December  | 
| 50.0309 | 2007 | February  | 
| 50.0602 | 2007 | January   | 
| 50.4556 | 2007 | July      | 
| 49.4132 | 2007 | June      | 
| 50.4012 | 2007 | March     | 
| 49.9563 | 2007 | May       |
etc...

I tested this using a integer value field that has a random value for every 15 minutes over the span of 4 years. The query executed in ~0.25s on my test server. (Which is a 5 year old gamer PC, by the way)

[bergy]

Atli,

This is perfect thanks - I'm assuming I'll have to convert dates into how MySQL likes them, but that shouldn't be a big deal. We're holding data on an embedded industrial PC so it's good to know your query times on your 5 year old machine. Thanks again!