Search code examples
mysqldatabasephpmyadminmysql-error-1064weather

MySQL phpMyAdmin average value counting event for current datetime

I have such a query about MySQL, which I am not fully familiar with, but I have to solve one problem. I have two tables, ie stats where I put my data (temperature, humidity) from the weather station and the avg_temp table, where I always need to save the average of the values obtained in stats for the given day. So I need to create an event in phpMyAdmin in a way that starts everyday at 23:55 and calculates the average of the temperature and humidity from that day and inserts it to the avg_temp table.

There're some basic structures of my tables:

avg_temp table:

avg_temp table

stats table:

stats table

It might be something like this, but it doesn't work for me, but I hope you get the idea about what I need to accomplish from that. 

SELECT `timestamp`,AVG(`humidity`) as avgHumid
FROM stats
GROUP BY `timestamp`
SELECT `timestamp`,AVG(`temperature_C`) as avgTemp
FROM stats
GROUP BY `timestamp`
INSERT INTO avg_temp (avg_temp, avg_humid) values('$avgTemp', '$avgHumid')
Solution

  • Here's the solution I found, which simply solves the problem that I had...

    SET @avgTemp = (SELECT AVG(temperature_C) AS avgTemp FROM (SELECT temperature_C FROM stats ORDER BY timestamp DESC LIMIT 144) a); 
SET @avgHumid = (SELECT AVG(humidity) AS avgHumid FROM (SELECT humidity FROM stats ORDER BY timestamp DESC LIMIT 144) a); 
SET @oldID = (SELECT MAX(id) FROM avg_temp ORDER BY timestamp);
SET @newID = @oldID + 1;
INSERT INTO avg_temp (id, avg_temp, avg_humid) values(@newID, @avgTemp, @avgHumid);