InfluxDb query to return the no of records in a given timespan

Question

Im using time series analysis and I'm using influx db to plot interesting graphs.

So here's the use case

I'm building an IOT project to monitor my desk plant. every time the moisture goes down to 10%, I trigger to turn on the pump and register that activity on influxdb as well. every time pump goes off, another activity is registered.

Here is the point in the line protocol which gets saved

activities,action=pump,sensor=628cfbddf5d8735e30504021,controller=61dd1bfa47e99385a55d4853,org=61c06a2adcfa254dda756796,service=61c06a7edcfa254dda756799,type=on count=1i 1653406685616

activities,action=pump,sensor=628cfbddf5d8735e30504021,controller=61dd1bfa47e99385a55d4853,org=61c06a2adcfa254dda756796,service=61c06a7edcfa254dda756799,type=off count=1i 1653406871246

As you can see, I have a few ids stored as tags and only count=1 is stored in the field.

So here is the challenge

I want to plot a graph of no. of times the pump was switched VS every day for the past 30 days. I've tried a few approaches but could not come to find a way to solve this issue.

I really hope this kind of use case is possible to achieve. Thank you

Answer 1

You could try following query:

SELECT COUNT("count")/2 FROM "your_measurement" 
WHERE time >= '20XX-XX-XXT00:00:00Z' AND time <= '20XX-XX-XXT23:59:29Z' 
GROUP BY time(24h)

The query returns the number of non-null field values in the count field key and divide it to half (assuming that every switch on comes with a switch off roughly). It covers the time range between 20XX-XX-XXT00:00:00Z and 20XX-XX-XXT23:59:29Z and groups results into 24-hour time intervals and per tag.

If you need exact switch on occurrences, then you could update above query:

SELECT COUNT("count")/2 FROM "your_measurement" 
WHERE time >= '20XX-XX-XXT00:00:00Z' AND time <= '20XX-XX-XXT23:59:29Z'
AND type='on'
GROUP BY time(24h)

Similarly for switch off case:

SELECT COUNT("count")/2 FROM "your_measurement" 
WHERE time >= '20XX-XX-XXT00:00:00Z' AND time <= '20XX-XX-XXT23:59:29Z'
AND type='off'
GROUP BY time(24h)

See more details here.