Calculating water consumption

Fluxlang
1

I have:

  • A water well
  • A water tank
  • A water level sensor in the water tank
  • A pump which pumps water from the well to the tank

I can log:

  • Water level in the water tank (‘Reservoir level’ below)
  • Pump on/off status (‘Pump Run Status’ below)

image
image1416×446 50 KB

What I would like to do: determine the difference in water level (max-min) during the periods when the pump is off (which I can then use to calculate water consumption during the periods when the pump is off).

How would you go about this? I’m happy to re-start the setup from scratch if there is a better way to store the data in order to perform queries. Currently I have:

  • Bucket: ‘water’
    • Measurement: ‘raw’
      • Field: ‘water level’
      • Field: ‘pump onoff’

I have been playing with queries in the Flux language and am somewhat able to assign pump status to specific timestamps in the water level table (though this isn’t always efficient), but I get most stuck working out how to ‘window’ the data so that I can calculate the min and max values - the pump does not switch on at a set time: it is purely dependent on the level of the water tank, so the off periods are not consistent in start time or duration.

Happy for any thoughts!

2

As an example of what I can do:

import "join"

level=from(bucket: "water")
  |> range(start: -1d)
  |> filter(fn: (r) => r["_measurement"] == "raw")
  |> filter(fn: (r) => r["_field"] == "Reservoir Level")
  |> truncateTimeColumn(unit: 1m)
  |> group(columns:["_time"])
  |> mean() 

pump=from(bucket: "water")
  |> range(start: -1d)
  |> filter(fn: (r) => r["_measurement"] == "raw")
  |> filter(fn: (r) => r["_field"] == "Pump Run Status")
  |> truncateTimeColumn(unit: 1m)
  |> group(columns:["_time"])

data=join.time(left: level, right: pump, as: (l, r) => ({l with pump_value: r._value}))
  |> filter(fn: (r) => r["pump_value"] != 1)
  |> group(columns: ["pump_value"])
  |> yield(name: "data")

This yields a table of water tank levels when the pump is off. What I would like to do next is split (window?) that table where the gap between consecutive timestamps is greater than 1 minute - this would leave me with separate tables for each pump-off period.

But I’m not sure how to accomplish that, as all the examples I see are related to windowing with respect to a fixed time period, not a gap in the timestamps…

3

Hello @dunbird,
Unfortunately there isn’t a good solution for this with Flux. I recommend using a client library and executing that logic in the language of your choice. Maybe utilizing smoothing and derivates = 0 for an approximation.