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)
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
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”,
