Storing duration in InfluxDB

Store
1

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:

  1. 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

  2. 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

  3. 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

2

If I were you I would store every state change with database insertions;

From on to off
152311837, pump1, 1
152311838, pump1, 0

From off to on
152311837, pump1, 0
152311838, pump1, 1

And if you want to figure out how long pump1 has been working just use an integration query like this:

SELECT integral(value) FROM "yourdb"."yourmeasurement" WHERE "tag" = 'pump1' and time > x and time < y

If you query like this, you get a value which represents all the seconds your pump has been on, thus giving you a duration :slight_smile:.

4

Hi @confususs,

(Had to rewrite that last reply!)

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.

Thanks!

5

Take a look at this syntax

select integral(field,1s)

Or here’s how I do this (in Python):

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).

6

Note, try this with and without “fill(previous)” and report back on the difference.

:wink:

7

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.

8

@confususs Yes, I was making that a little hard… I see how it works now. Thanks for very much!

@frank Thanks as well for the suggestions. I didn’t actually get to try it but have the integrate function working as expected now.

9

You’re welcome!

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.