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.
| Metric | Value | Date |
|---|---|---|
| pv_generation_energy | 5634.1 | 2021-10-18 16:45:00 |
| pv_generation_energy | 5623.3 | 2021-10-18 16:40:00 |
| pv_generation_energy | 5622.4 | 2021-10-18 16:35:00 |
| pv_generation_energy | 5619.5 | 2021-10-18 16:30:00 |
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.
CodePudding user response：
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
CodePudding user response：
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