Do you have the ability to change from InfluxQL to Flux? This sort of thing is much easier with Flux. If you post a sample of your data, me or someone can probably work out the Flux query to try out some suggested solutions.
If you are on the fence, ask @monstereye how his switch to Flux went.
SELECT spread("value") FROM "mqtt-client.0.shellies.Mikrowelle.relay.0.energy" WHERE $timeFilter GROUP BY time(1d) fill(null)
So I just want to ignore that one data record where it starts over from 0.
I actually would prefer to stay with InfluxDB 1.8 to have the convinent graphical settings possibility in Grafana, which I would miss with InfluxDB 2.x.
What is a problem with NON_NEGATIVE_DERIVATIVE? I guess you need to use subqueries (so no graphical interface, but pure InfluxQL).
1.) inner query NON_NEGATIVE_DERIVATIVE with full granularity → so you will have rate of change
2.) outer query SUM + some time multiplication of results from inner query with daily aggregation
Thanks for the hint with the sum, the following query solved it for me:
SELECT spread("ccs") FROM
CUMULATIVE_SUM(SUM(nnd)) as ccs
SELECT NON_NEGATIVE_DIFFERENCE("value") AS nnd FROM "mqtt-client.0.shellies.Mikrowelle.relay.0.energy"
) GROUP BY time(1s)) GROUP BY time(1d)
Thank you for this working query I would never have been able to set myself. @All : please note that this query will give a cumulative graph starting from 0 on the specified
$timeFilter timespan, even using a permanent counter (i.e in my case, a total working hours counter on my projector).