Hi all,
Looking for advice on how to setup my measurements…
I have 2 Stenner peristaltic pumps that I will be installing on my pool system to dose chlorine and acid. The pumps can dispense a fixed 3GPD (gallons per day) of chemical. The pumps will be turned on/off during the day by my code. I want to be able to calculate the duration of each pump for it’s individual run times and also the cumulative duration it ran over 1d, 1w, etc.
Below mockups show pump 1 turning on/off then pump 2 turning on, but in reality, the run times could overlap.
I think my options are:
Basic - just store the on/off times for each pump. Calculate duration from off time minus on time.
eg
time______tag____status
152311837, pump1, ‘on’
152311899, pump1, ‘off’
152311944, pump2, ‘on’
etc
More data - store the state of each pump each for each minute. Calculate first off time minus first on time for each pump.
eg
time______tag____status
152311837, pump1, ‘on’
152311899, pump1, ‘on’
152311944, pump1, ‘on’
152311946, pump2, ‘on’
etc
Store one of the two options above, but also store the duration (only with the off values? or on a separate line?)?
eg
time______tag____status___duration
152311837, pump1, ‘on’
152311899, pump1, ‘off’, 3 minutes
152311944, pump1, ‘on’
152311954, pump2, ‘on’
etc
//or
time______tag____status___duration
152311837, pump1, ‘on’
152311899, pump1, ‘off’
152311899, pump1, ‘duration’, 3 minutes
152311944, pump1, ‘on’
152311954, pump2, ‘on’
etc
Would appreciate some advice as to what is the best approach, or if there is one that I’m missing that would be better.
Thanks
Here is my sample data cooked up via my coding that simulates turning on/off a single pump every minute. I’m storing my calculated duration, int values and string values for the states changes.
> select * from tank_pumps where time >= '2018-04-11T21:12:31Z' and time <= '2018-04-11T21:20:10Z'
name: tank_pumps
time duration_min duration_sec from from_int from_str to to_int to_str type
---- ------------ ------------ ---- -------- -------- -- ------ ------ ----
2018-04-11T21:12:31Z 0 off 1 on state_change
2018-04-11T21:13:37Z 1 60 1 on 0 off state_change
2018-04-11T21:14:42Z 0 off 1 on state_change
2018-04-11T21:15:48Z 1 60 1 on 0 off state_change
2018-04-11T21:16:53Z 0 off 1 on state_change
2018-04-11T21:17:59Z 1 60 1 on 0 off state_change
2018-04-11T21:19:05Z 0 off 1 on state_change
2018-04-11T21:20:10Z 1 60 1 on 0 off state_change
Here are the results of the integral() operation:
> select integral(*) from tank_pumps where time >= '2018-04-11T21:12:31Z' and time <= '2018-04-11T21:20:10Z'
name: tank_pumps
time integral_duration_min integral_duration_sec integral_from integral_from_int integral_to integral_to_int
---- --------------------- --------------------- ------------- ----------------- ----------- ---------------
2018-04-11T21:12:31Z 393 23580 0 393 394 0
I could of course run a sum(duration_min) and get 4 as the value, but in reality, the pump is on longer than that.
on = 12:31 off = 13:37 duration = 1min 6 sec
This happens 4 times for a total of ~4min 24seconds (264 total seconds).
The results of the integral are 393s or (393/60)=6.55 minutes.
Am I doing something wrong? Or rather, what am I doing wrong?
I could calculate my duration data on the backend better (it is now just hardcoded to the values), but I also have historical data so would really like to understand the integral operation better.
q=“select mean(rpm) from compressor where mac =’{}’ and time > ‘{}’ and time < ‘{}’ group by time(2m)”.format(asset_id,ifx_tk,ifx_et)
l=list(iclient.query(q).get_points())
if len(l) > 0:
print(“Hours: {}”.format(hours))
adder = sum(1 for i in l if i[‘mean’] is not None)
hours = float(adder)/30.0 + hours
print("-Hours: {} +{} minutes".format(hours,adder))
else:
pass
And let me explain. By selecting mean(rpm) I can specify time(), in my case 2 minutes. But you can choose any interval you like. The more intervals you choose, the more accurate your result, but at the expense of more memory/compute time.
So if you just store the value when it changes (0 for Off, 1 for On).
select mean(pump_state) from your measurement where id = ‘pump1’ group by time (1s)
This will give you a result set with values every 1 second. Then you can count up the values in your result set that are non-zero and that is the number of seconds that the pump was on in the time period. Of course it is an approximation because the resolution is only to 1 second. So you might miss just a bit less than a second at each end of the interval.
Note that if you are examining quite large intervals of time, the number of values with 1-second resolution is quite large. I have that situation from time to time. So I put the above in a loop and do it in one-hour chunks each time (and I am using 2-minute interval). Then I advance the start time and the end time by one hour (up to the end of the interval).
Heya! The option @frank suggests is also a possibility. Only that way you require additional scripting to merge the queried groups.
I think you’re making it too complicated. The only value you need to store is whether the pump is on or off.
So your whole table can look like this:
> select * from tank_pumps where time >= '2018-04-11T21:12:31Z' and time <= '2018-04-11T21:20:10Z'
name: tank_pumps
time on
---- -----
2018-04-11T21:12:31Z 0
2018-04-11T21:12:32Z 1
2018-04-11T21:13:37Z 1
2018-04-11T21:14:38Z 0
If you integrate like this:
> select integral(on) from tank_pumps where time ...
You’ll get 5~ as a result, because your pump has been on for 5 seconds. Integral basically iterates over every second in the dataset and increments its counter.
You don’t need to store duration as every value is stored with a timestamp.
Keep in mind that influxdb interpolates your data. So if you have something like this:
> select * from tank_pumps where time >= '2018-04-11T21:12:31Z' and time <= '2018-04-11T21:20:10Z'
name: tank_pumps
time on
---- -----
2018-04-11T21:12:00Z 0
2018-04-11T21:13:00Z 1
And you take the integral, you will get something like 30. This because a line will be drawn for every second between 0 and one and the area under the line will be summed.
This is why I advice you to store 2 values right after one another when the pump has a state change.