Aggregate daily consumption from meter readings

InfluxDB 2
,
1

Hello
I am using influxdb 2 with flux language. I am trying to do something that someone said could be done with influxdb.
The raw data is meter readings at irregular intervals. These were read by people, for example. From these meter readings, the daily or monthly (depending on the timerange) consumption is to be output. Where data is missing (between 2 readings), it should simply be interpolated.

I have tried with “difference”, “aggregateWindow”, “aggregate.rate” and I don’t know what else.
I am new to influxdb and the flux language is, to be honest, not easy to understand.
I have never been this close to the target… but something is wrong.

Ohne Titel 2
Ohne Titel 21007×1198 133 KB

In the meantime, I doubt that it is possible and am considering calculating the data outside and feeding it back in.
Or does anyone know any advice?
Many thank.

2

Interesting question, should actually be a more common use case for a time series database? :thinking:
Can you post your flux snippet?
Unfortunately, I don’t know much about flux (yet) either… :neutral_face:

I would perhaps try the derivative() function - if I understood correctly, you are more interested in the increase than the absolute difference between the non-equidistant measuring points?

3

Hi Franky
That my code. I tried with derivative() and difference() too.

import "experimental/aggregate"
from(bucket: "testMeterIndex")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "W01_Z" and r._field == "value")
  |> aggregate.rate(every: 1d, unit: 1d)
  |> fill(usePrevious: true)
  |> aggregateWindow(every: 1mo, fn: sum)

Actually, difference() would already be correct. The problem is that I cannot interpolate (or don’t know how). If I could, for example, sample the meter readings at a regular interval and interpolate them, that would not be a problem. Or calculate the difference and then distribute it to the missing intervals.
My first thought was to use the difference function in aggregateWindow. But that is not possible (see https://github.com/influxdata/flux/issues/2327).

Then I tried this:

from(bucket: "testMeterIndex")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "W01_Z" and r._field == "value")
  |> difference(nonNegative: true)
  |> aggregateWindow(every: 1mo, fn: sum)
  |> fill(value: 0.0)

This data is already correct. But the missing data, which I fill with 0, should be filled with the difference divided by the number of periods.

This use case is not so far-fetched. If the data is produced by a PLC and there is an outage over a few days, the data would also have to be interpolated and not simply shown as 0 for a few days and then a large consumption.