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("SE LECT * FROM digitemp WHERE DATE_SUB(CURREN T_DATE, INTERVAL 1 DAY) < time ORDER BY time ASC LIMIT 288");
Today
$result = mysql_query("SE LECT * 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(CURREN T_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(CURREN T_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(CURREN T_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(CURREN T_DATE) GROUP BY DATE( time ), HOUR( time ) ORDER BY time ASC

[luxsphinx]

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

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.