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