Delta between month / cumulated Value / Power consumption per month

Hi,

can someone help me with the calculation of the difference between the month?

The value which has written in the DB is a cumulated.

My wish is in the end to have a bar chart. Each month a bar with the consumed power.


I wasn’t able to solve the problem in the meantime. Isn’t someone here who can help me, really ?

with data as (SELECT start, MAX(state) value FROM homeassistant.statistics
  where metadata_id = 121 
  GROUP BY month(start), year(start)
  order by start)
select start, value - coalesce(lag(value, 1) over (order by start asc), 0) as delta from data

Does this help?

1 Like