# Calculate energy consumption based on counter value

Hello, I’d have a question
I have a time series with power data (W) and energy counter value data (Wh).

time power (W) energy (kWh)
2023.09.14 13:59 48 4921
2023.09.14 14:00 51 4921
2023.09.14 14:01 50 4921
2023.09.14 14:02 250 4921
2023.09.14 14:03 300 4922
2023.09.14 14:04 420 4923
2023.09.14 14:05 980 4924
2023.09.14 14:06 1200 4925
2023.09.14 14:07 1100 4926
2023.09.14 14:08 1000 4927
2023.09.14 14:09 900 4928
2023.09.14 14:10 750 4929
2023.09.14 14:11 400 4929
2023.09.14 14:12 250 4930
2023.09.14 14:13 60 4930
2023.09.14 14:14 51 4930
2023.09.14 14:15 49 4930

I’d like to calculate energy consumption for the last cycle as follows:
Calculate the difference of energy counter value between end of the period (now) and when power is exceed 100 W. In this example counter value now is 4930 and when power exceeded 100 W (14:02) it was 4921 so the result is 9 kWh.
I use Grafana 10 and MySQL (Mariadb)

welcome @fjanni

Your question is really more mysql query language than it is grafana. While someone might answer this question for you, might be better to ask this in a mariadb or my sql forum.

here are some hints on functions you might be able to use to get what you want

Hi, Can you share the equation.

Thank you for the idea, LAG is suitable to calculate difference between 2 counter values.
This is the query which is working:
SELECT
\$__timeGroupAlias(time,\$__interval),
power_sum/1000 as “power”,
energy_consumed - lag(energy_consumed) over(order by time) as “energy consumption”,
energy_consumed as “energy counter”
FROM ems.janitza_ekiln_nabertherm
where \$__timeFilter(time)
group by 1
order by time

I wanted to SUM the differences to calculate the consumption within the period but it resulted error message:
SUM (energy_consumed - lag(energy_consumed) over(order by time)) as “energy consumption”,

Hi, I need to summarize the counter differences within the selected period (interval)