Incorrect results with difference() because it doesn't initialize with the last value before query time range

[fetzerch]

Currently difference (and most likely also derivative) return incorrect results since they don't initialize the first value with the previous value before the query time range. They only allow to completely drop the first value (keepFirst) or assume a first value of 0 (initialZero). This doesn't work well with e.g. counters.

Example:
I have a bucket with counters for water & gas consumption and want to calculate the daily consumption:
The counter increases by 1 every time a certain amount has been consumed (e.g. 1 liter for water).

I'm using this query:

from(bucket: "smartmeter")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "water.consumption.counter")
  |> difference()
  |> aggregateWindow(every: 1d, fn: sum, timeSrc: "_start")

If I select a single day only, I get an incorrect value for this day:

2022-04-06 00:00:00 -> 0.221

If I select also the previous day, I get the correct value, but again an incorrect value for the previous day:

2022-04-05 00:00:00 -> 0.156
2022-04-06 00:00:00 -> 0.222  <-- This is the correct value

In this case initialZero doesn't help, because in the example the first entry would have to be initialized with 1.

It would be really nice if difference/derivative could be fixed to initialize properly.

A possible workaround would be to get #702 implemented. I assume range(start: v.timeRangeStart, stop: v.timeRangeStop, previousN=1) together with difference(keepFirst: false) would solve the problem.

Note that this has been requested already some time ago for InfluxQL in influxdata/influxdb#5943.

[github-actions]

This issue has had no recent activity and will be closed soon.

[fetzerch]

I think it's worth to keep this open, because the issue still exists.

[github-actions]

This issue has had no recent activity and will be closed soon.

[fetzerch]

I think it's worth to keep this open, because the issue still exists.

[github-actions]

This issue has had no recent activity and will be closed soon.

[fetzerch]

I think it's worth to keep this open, because the issue still exists.

[github-actions]

This issue has had no recent activity and will be closed soon.

[fetzerch]

Issue still exists, but I cannot reopen it.