How to calculate difference between a value, based on start and end datetime

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.

Answers:

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.

Method 1

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

Method 2

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