January 5th, 2013, 04:24 AM
Hourly averages from a table with multiple rows per hour
I have a table with rows of temperature data. 2 columns - datetime (which is a datetime column) and temp1.
To get the hourly averages at the moment I use a PHP loop to increment start and end times and run multiple queries.
Is it possible to get hourly averages in one query?
January 5th, 2013, 04:37 AM
, start_time + INTERVAL 1 HOUR AS end_time
, AVG(temp1) AS avg_temp
FROM ( SELECT FROM_UNIXTIME(FLOOR(UNIX_TIMESTAMP(`datetime`)/3600)) AS start_time
FROM daTable ) AS data
January 5th, 2013, 08:47 AM
That is amazing!
Thank you very much.