使用SQL从前一天检索数据

Question

I'm running a php script that retrieves the AVG of some data from 1 previous day:

$yesterday1=date('Y-m-d H:i:s', strtotime('-1 day')); 
$strSQLYesterDay1=" SELECT AVG(Temperature) As TAverage1 FROM minute WHERE ";
$strSQLYesterDay1=$strSQLYesterDay1. "DateTime LIKE '" . substr ($yesterday1,0,10)."%'";

What to I change in this php/sql script so that instead of "1 day" ago, it's "1 hour" ago?

Answer 1

you could also do it all in mysql:

SELECT AVG(Temperature) As TAverage1
FROM minute
WHERE DateTime BETWEEN TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 HOUR)) AND NOW()

this will get the AVG of all record with DateTime between now and an hour ago

that way you don't need to build the query with php, and you don't need to use string comparison.