CODEWITHSUNDEEP
phpmysqlsql
mmmbaileys
mmmbaileys

Reputation: 1243

Retrieve data from previous day using SQL

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?

Upvotes: 0

Views: 499

Answers (2)

Dalen
Dalen

Reputation: 8986

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.

Upvotes: 4

Raisen
Raisen

Reputation: 4495

You change on this line:

$yesterday1=date('Y-m-d H:i:s', strtotime('-1 hour'));

Upvotes: 1

Related Questions