Difference with NEXT row vs. PREVIOUS?

Hi - I have 2 independent streams: one with water meter reading (in gallons) and another with irrigation zone start/stops from my irrigation controller. I want to determine how much water is used while each zone is running.

I have been able to use union, fill, difference to combine into a single table with a row with the water meter reading when a zone turns on and when a zone turns off (zone = 0). I do a difference at that point on the water meter reading to get the amount of water used for a particular zone (waterdelta). Unfortunately, since difference calculates the delta from the previous row, the amount of water used for a zone shows up in the row when the zone is turned off (zone =0).

Looking for suggestions:

  • is there some way to calculate the difference to the next row vs. previous?
  • Can I shift each entry in the waterdelta column by 1 row?

P.S. I’m running in influxdb 1.8 as I’m running on a 32b raspberry pi.

Here’s my complete script:

zoneRunning = from(bucket: “openhab_db/autogen”)
|> range(start: -7d)
|> filter(fn: (r) => r._measurement == “rachio_active_number” and r._field == “value”)
|> duplicate(column: “_value”, as: “zone”)
|> keep(columns: ["_time", “zone”])

waterUsage = from(bucket: “openhab_db/autogen”)
|> range(start: -7d)
|> filter(fn: (r) => r._measurement == “FlumeTechMeterDevice_CumulativeUsage” and r._field == “value”)
|> duplicate(column: “_value”, as: “water”)
|> keep(columns: ["_time", “water”])

union(tables:[zoneRunning, waterUsage])
|> sort(columns: ["_time"])
|> fill(column: “water”, usePrevious: true)
|> fill(column: “zone”, usePrevious: true)
// find state changes of zone
|> duplicate(column: “zone”, as: “difference”)
|> difference(columns: [“difference”])
|> filter(fn: (r) => r.difference != 0)
|> drop(columns: [“difference”])
// difference in water levels at zone changes
|> duplicate(column: “water”, as: “waterdelta”)
|> difference(columns: [“waterdelta”])

@jsjames Probably the simplest way would be to reverse the sort order of your data before difference(), the resorting after:

// ...

union(tables:[zoneRunning, waterUsage])
    |> fill(column: “water”, usePrevious: true)
    |> fill(column: “zone”, usePrevious: true)
    // find state changes of zone
    |> duplicate(column: “zone”, as: “difference”)
    |> difference(columns: [“difference”])
    |> filter(fn: (r) => r.difference != 0)
    |> drop(columns: [“difference”])
    // difference in water levels at zone changes
    |> duplicate(column: “water”, as: “waterdelta”)
    |> sort(columns: ["_time"], desc: true)
    |> difference(columns: [“waterdelta”])
    |> sort(columns: ["_time"], desc: false)

Thanks for creative thinking here!! That works well.