Database / MySQL - Calculate Energy Consumption for given table

Question

i would like to calculate the sum of the energy consumption from this openhab persistence table, which contains the rows for current energy consumption of my lamps:

+---------------------+--------+
| time                | value  |
+---------------------+--------+
| 2022-10-08 21:48:08 |      0 |
| 2022-10-08 21:56:52 |  13.48 |
| 2022-10-08 21:56:57 |  13.63 |
| 2022-10-08 21:57:06 |  13.68 |
| 2022-10-08 21:57:09 |  13.63 |
| 2022-10-08 21:57:22 |  13.68 |
| 2022-10-08 21:57:39 |  13.73 |
| 2022-10-08 21:57:52 |  13.68 |
| 2022-10-08 21:57:54 |  13.73 |
| 2022-10-08 21:58:06 |  13.78 |
| 2022-10-08 21:58:09 |  13.73 |
| 2022-10-08 21:58:22 |  13.78 |
| 2022-10-08 21:58:37 |  13.73 |
| 2022-10-08 21:58:39 |  13.78 |
| 2022-10-08 21:58:54 |  13.83 |
| 2022-10-08 21:59:06 |  13.78 |
| 2022-10-08 21:59:22 |  13.73 |
| 2022-10-08 21:59:24 |  13.78 |
| 2022-10-08 22:00:11 |  13.73 |

this table represents the power consumption value at the current date and time.

means: the power consumption didn´t change until the next dataset is inserted with a new value. the power-consumtion is constant for the time between the current and the following dataset with different value.

i have no idea how to get which wattage was for what duration. my goal is to get the sum, which should be in watthours oder kilowatthours.

anybody has any hints for me how to do it? thx

Edit: 2022-10-08 21:59:06 to 2022-10-08 21:59:22 the current wattage is 13.78.

13.78W for 16 seconds: 13.78W * 16s = 220,48 Ws

searched here, is the sum of the obove calculated Wattseconds.

Answer 1

We first have to JOIN the table itself with the following row (nearest time after current rows time):

SELECT A.time as time1, B.time as time2, A.value as value1, B.value as value2 FROM OpenHAB.item0022 as A left join OpenHAB.item0022 as B ON B.time = (SELECT time FROM item0022 WHERE time > A.time ORDER BY time ASC LIMIT 1)

after this is done, we can get the difference in seconds between the current and the following dataset, with some calculations, and divide it by 60 seconds * 60 minutes * 1000 (Watt to kWatt) we get the correct result.

SELECT SUM(TIMESTAMPDIFF(SECOND,time1,time2)*value1)/(60*60*1000) as Sum_kWh FROM 

    (
SELECT A.time as time1, B.time as time2, A.value as value1, B.value as value2 FROM OpenHAB.item0022 as A left join OpenHAB.item0022 as B ON B.time = (SELECT time FROM item0022 WHERE time > A.time ORDER BY time ASC LIMIT 1)
) as q1 WHERE value1 <> 0