mysql how to get max value for a certain day?

Question

I have a table which holds sensor data:

id, name(varchar), value(float), time(datetime)

Now i need to select the max value of a certain day.

I'm getting a variable number like "1" which means, it should be the highest value of yesterday.

So how can i use this number to get the max value of this day?

I know so far there is this construct:

subdate(CURDATE(), ".$day.")

And also got a query for max value:

SELECT MAX(value) AS value FROM ".$tablename." WHERE sensor_id=? AND value_id=?

But i have problems to combine this with the date...

Hopefully you can help. thx

`AND \`time\` = '2018-05-01 10:00:00'` or `AND DATE(\`time\`) = '2018-05-01'` — RiggsFolly, May 05 '18 at 21:10
Add some sample data and expected result as the columns in the table and those in the query you provided are not coherent. — cdaiga, May 05 '18 at 21:30

score 0 · Answer 1 · answered May 05 '18 at 22:58

If your variable holding yesterday is $date_less and $datetime is the variable holding currnet date then your code look like this

$datetime   = date("Y-m-d H:i:s");
$date_less  = 1;
"SELECT MAX(value) AS value FROM ".$tablename." WHERE sensor_id=? AND value_id=? and `time` = ".date('Y-m-d H:i:s', strtotime($datetime.' -'.$date_less.' days'));

for more information on adding days in date you can see this link

Nick · Accepted Answer · 2018-05-05T23:54:14.450

0

Based on the information in your question (and using your variable names), I think this query will work for you:

$query = "SELECT MAX(value) AS value 
          FROM " . $tablename . "
          WHERE sensor_id=? AND
                value_id=? AND 
                DATE(time)=SUBDATE(CURDATE()," . $days . ")";

edited May 05 '18 at 23:54

answered May 05 '18 at 23:37

Nick

138,499
22
57
95

thx exactly what i looked for. Exactly this part i missed in my query: DATE(time)=. – user3882511 May 06 '18 at 07:35

mysql how to get max value for a certain day?

2 Answers2