I am using SQL to query a MySQL database. The records show a houses PV energy generation over time. The total PV energy generated is updated every 5 minutes. I have several months worth of data.
How do I calculate the difference between the two values, based on a start and end date, showing the total PV energy generation?
In the instance above, stating a start date of 2021-10-18 16:45:00 and end date of 2021-10-18 16:30:00 would result in a value of 4.6.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
select (t1.Value - t2.Value) your_result from tbl1 t1 inner join tbl1 t2 on t1.Date = '2021-10-18 16:45:00' and t2.Date = '2021-10-18 16:30:00'
simply join self and minus the values
Using MAX and MIN you could simply do
SELECT MAX(metric) - MIN(metric) as generated FROM tablename WHERE `date` BETWEEN '2021-10-18 16:30:00' AND '2021-10-18 16:40:00'
From you data it generates
14.6 which I think is the correct answer
5634.1 - 5619.5 = 14.6
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0