How to pull hourly averages for a week's worth of data (5 minute intervals) in MySQL?

[luxsphinx]

OS: Ubuntu Linux
PHP: v5.3.2
MySQL: v5.1.41

Hello all,

I'm new to Bytes, but I'm hoping that someone here will be able to help me as I learn PHP and MySQL. With any luck, I'll understand it enough that I may be able to help others in the future.

Hopefully I posted this to the right section (it's for a PHP file that makes a call to a MySQL database) - my apologies if I should have went to the MySQL area. As the title states, I am trying to pull the hourly averages for a week's worth of data that was taken at 5 minute intervals from a MySQL database. So that the situation clear, I'll explain what I am attempting to do. I have a database that stores temperature data to a table with five columns (dtkey, time, SerialNumber, and Fahrenheit). Data is collected every five minutes with the result below:

dtKey     time             SerialNumber         Fahrenheit
-------------------------------------------------------------------
1409     2010-06-23 00:00:02     10C4A9F501080055     73.51
1410     2010-06-23 00:05:02     10C4A9F501080055     72.85
1411     2010-06-23 00:10:02     10C4A9F501080055     72.51
1412     2010-06-23 00:15:02     10C4A9F501080055     73.51
1413     2010-06-23 00:20:02     10C4A9F501080055     73.85
1414     2010-06-23 00:25:02     10C4A9F501080055     73.51
1415     2010-06-23 00:30:02     10C4A9F501080055     73.85
1416     2010-06-23 00:35:02     10C4A9F501080055     73.85
1417     2010-06-23 00:40:03     10C4A9F501080055     73.96
1418     2010-06-23 00:45:02     10C4A9F501080055     73.74
1419     2010-06-23 00:50:02     10C4A9F501080055     73.85
1420     2010-06-23 00:55:03     10C4A9F501080055     72.72
1421     2010-06-23 01:00:02     10C4A9F501080055     73.74
1422     2010-06-23 01:05:02     10C4A9F501080055     72.39
1423     2010-06-23 01:10:03     10C4A9F501080055     73.96
1424     2010-06-23 01:15:02     10C4A9F501080055     73.51
1425     2010-06-23 01:20:02     10C4A9F501080055     73.74
1426     2010-06-23 01:25:03     10C4A9F501080055     74.08
1427     2010-06-23 01:30:02     10C4A9F501080055     73.96
1428     2010-06-23 01:35:02     10C4A9F501080055     73.96
1429     2010-06-23 01:40:02     10C4A9F501080055     73.85
1430     2010-06-23 01:45:03     10C4A9F501080055     69.91
1431     2010-06-23 01:50:02     10C4A9F501080055     67.78
1432     2010-06-23 01:55:02     10C4A9F501080055     66.65
1433     2010-06-23 02:00:03     10C4A9F501080055     65.86
1434     2010-06-23 02:05:02     10C4A9F501080055     65.75
...and so on.
 

I am trying to chart this data using PHP and Flash on a webpage. The webpage will allow you to choose which of the eight charts you would like to load (Yesterday, Today, Last Week, This Week, Last Month, This Month, Last Year, or This Year).

I've already gotten the Yesterday and Today charts working with these codes:

Yesterday
$result = mysql_query("SELECT * FROM digitemp WHERE DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) < time ORDER BY time ASC LIMIT 288");
Today
$result = mysql_query("SELECT * FROM digitemp WHERE time > CURRENT_DATE ORDER BY time ASC");

So for the "This Week" chart, I'm trying to come up with a line code that will take at all of the data from the current week (Sunday through Saturday not the past 7 days) and calculate the average temperature for each hour. The result would have 24 temperature values for each day instead of 288 values, arranged in time order.

Based on the example data above, it would take the temperatures from midnight to 1 (aka 00:00:02 to 00:55:03), average it, and return a value of 73.48. The same would be done for 01:00:02 to 01:55:02 and so on. The values would then be arrange in time and day order (Sunday through Saturday). For the "Last Week" chart, it would be similar, but somehow subtract the current week's days.

I've tried a bunch of different lines, but the most recent attempt was:
SELECT AVG( Fahrenheit ), time FROM temparchive WHERE YEARWEEK(time) = YEARWEEK(CURRENT_DATE) GROUP BY HOUR( time ) ORDER BY time ASC

However, this does not work.

Does anyone have thoughts on how to do this? Would it be a simple matter to adapt the code for the 'This Week' and 'Last Week' charts to the 'This Month' and 'Last Month' charts?

Thank you for any help you can offer!

Regards,
Nick

[code green]

However, this does not work

It is not clear whether the problem is extracting data for the relevant period or getting the averages
I think you need to seperate out the specific problem, maybe break it down into two.
See if can be done via MySQL.
If not just get all data into PHP arrays which can easily be manipulated

[luxsphinx]

The problem seems to be getting the proper time period.

Running:
SELECT AVG( Fahrenheit ), time FROM temparchive WHERE YEARWEEK(time) = YEARWEEK(CURRENT_DATE) GROUP BY HOUR( time ) ORDER BY time ASC

...does give me the a set of hourly averages, but it combines all the days into one. So while three days worth of data should give me 72 averages, it instead gives me only 24 averages where all of the 1AM's (or in this case 01:xx:xx) from the three days are merged into one and averaged.

If I go the route of:
SELECT AVG( Fahrenheit ), time FROM temparchive WHERE YEARWEEK(time) = YEARWEEK(CURRENT_DATE) GROUP BY DATE( time ) ORDER BY time ASC

...I get average of each day. So three days give me 3 averages. I simply can't figure out how to first divide the data into separate days, and then divide the resulting data by hours.

[improvcornartist]

Maybe group by date, then hour:
SELECT AVG( Fahrenheit ), time FROM temparchive WHERE YEARWEEK(time) = YEARWEEK(CURRENT_DATE) GROUP BY DATE( time ), HOUR( time ) ORDER BY time ASC

[luxsphinx]

@improvcornartist
Thank you very much!

That did the trick perfectly. I had tried "DATE(HOUR(time))" and "HOUR(DATE(time))" but never tried "DATE(time), HOUR(time)".

This has been one of those very frustrating problems with a simple solution that was just escaping me. I can now move forward with setting up the other graphs and adding new sensors.

Once again, thank you.