Query that returns sum of last 10 minutes of data

I’m running InfluxDB 1.8.4 on an old RPi1 for testing. My weather station is set up to log rain incremental values as they are recorded. I am attempting to upload my data to PWS so that I can use it with an Orbit sprinkler that can pause irrigation during rain events. I could use an existing weather station, but I have found that doesn’t exactly work the way I want and I already have the data, so why not use it!

My existing query is:

select sum(*) from "rtl_433/Acurite-5n1/A/rain_increment_in_test" GROUP BY time(10m) ORDER BY time DESC LIMIT 1

When I run this query when there has been a lot of rain logged recently, it seems to work fine. When there has not been any rain, it returns null. When there has been rain within the last 10 minutes from the current time, it does not always return a value as I would have expected. I increased my LIMIT to 10 and found that the query returns time that is on the 10m of the hour rather than the last 10m from the current time. This means that if I query at 12:01, the query will return data for the last 1 minute rather than the previous 10 minutes I believe.

I found this topic that suggests there may be a workaround with continuous queries, but I’m not using those as I’m running this from Node Red. I’m not sure if it’s related anyway, but I figured I’d post what I found in case it helps.

Hello @bkenobi,
Have you tried adding a WHERE clause? like:

WHERE time > now() -10m

I attempted that but it did not seem to improve the situation.

select sum(*) from "rtl_433/Acurite-5n1/A/rain_increment_in_test" WHERE time > now() - 1d GROUP BY time(30s) ORDER BY time DESC LIMIT 10

I tried using this with now()-10s during testing but it appeared to work the same way. I’m not sure if I’m reading the output correctly. Here is the same query and response but looking at 10s as the metric.

> select sum(*) from "rtl_433/Acurite-5n1/A/rain_increment_in_test" WHERE time > now() - 10s GROUP BY time(10s) ORDER BY time DESC LIMIT 10
name: rtl_433/Acurite-5n1/A/rain_increment_in_test
time                 sum_value
----                 ---------
2021-08-19T15:46:50Z
2021-08-19T15:46:40Z 0.01

The way I read it, the output is still using even 10s intervals (15:46:40 and then 15:46:50). I was expecting to see something based on the time submitted which shouldn’t always be divisible by 10s.

If I use 30s as the metric it shows the same trend.


> select sum(*) from "rtl_433/Acurite-5n1/A/rain_increment_in_test" WHERE time > now() - 30s GROUP BY time(30s) ORDER BY time DESC LIMIT 10
name: rtl_433/Acurite-5n1/A/rain_increment_in_test
time                 sum_value
----                 ---------
2021-08-19T15:49:00Z 0.01
2021-08-19T15:48:30Z