By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,825 Members | 1,281 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,825 IT Pros & Developers. It's quick & easy.

Averaging Data by Date

bergy
P: 89
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!
Jan 23 '09 #1
Share this Question
Share on Google+
2 Replies


Atli
Expert 5K+
P: 5,058
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.     AVG(`Value`) AS `Average`,
  3.     YEAR(`Date`) AS `Year`,
  4.     MONTHNAME(`Date`) AS `Month`
  5. FROM `random`
  6. GROUP BY `Year`, `Month`;
Would give you and average for each month in that table.
Like:
Expand|Select|Wrap|Line Numbers
  1. +---------+------+-----------+
  2. | Average | Year | Month     |
  3. +---------+------+-----------+
  4. | 50.5726 | 2007 | April     | 
  5. | 50.4852 | 2007 | August    | 
  6. | 49.9294 | 2007 | December  | 
  7. | 50.0309 | 2007 | February  | 
  8. | 50.0602 | 2007 | January   | 
  9. | 50.4556 | 2007 | July      | 
  10. | 49.4132 | 2007 | June      | 
  11. | 50.4012 | 2007 | March     | 
  12. | 49.9563 | 2007 | May       |
  13. 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)
Jan 23 '09 #2

bergy
P: 89
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!
Jan 23 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.