如何计算两个日期之间的每小时，每日，每月和每年平均值？

Question

I am developing an application in PHP. I use MySql database for that. I want to calculate Hourly, Daily, Monthly and Yearly average between two dates.

My query for hourly average is

SELECT avg(value) 
FROM table_name  
WHERE (added_date BETWEEN '2014-01-29 00:00:00' AND '2014-01-31 00:00:00') 
GROUP BY DATE(added_date), HOUR(added_date)  
ORDER BY added_date ASC

Now i want to do the same query for calculate daily, monthly and yearly average. Please suggest solution. Thanks

Answer 1

Do you just mean something like these?

Hourly average

SELECT DATE(added_date), HOUR(added_date) , avg(value) 
FROM table_name  
WHERE (added_date BETWEEN '2013-01-29 00:00:00' AND '2014-01-31 00:00:00') 
GROUP BY DATE(added_date), HOUR(added_date)  
ORDER BY added_date ASC

Daily average

SELECT DATE(added_date) , avg(value) 
FROM table_name  
WHERE (added_date BETWEEN '2013-01-29 00:00:00' AND '2014-01-31 00:00:00') 
GROUP BY DATE(added_date)
ORDER BY added_date ASC

Monthly average

SELECT YEAR(added_date), MONTH(added_date), avg(value) 
FROM table_name  
WHERE (added_date BETWEEN '2013-01-29 00:00:00' AND '2014-01-31 00:00:00') 
GROUP BY YEAR(added_date), MONTH(added_date)
ORDER BY added_date ASC

Yearly average

SELECT YEAR(added_date), avg(value) 
FROM table_name  
WHERE (added_date BETWEEN '2013-01-29 00:00:00' AND '2014-01-31 00:00:00') 
GROUP BY YEAR(added_date)  
ORDER BY added_date ASC