Show amount of rain fall per hour

InfluxDB 2 Dashboards
,
1

I already raised the question over in the Grafana community, but they recommended to ask here. Since it is mainly a question about the right query that makes probably sense.

I have a rain sensor that sends every 10 minutes the total amount of rain fall it has seen.
I do some calculations and write the amount of rain fall (in mm, which is 1l rain per m²) between the last sensor reading and the current reading in an InfluxDB database.

Now I try to show in a time series panel the amount of fallen rain per hour at each point in time. I’m using InfluxQL as query language.

Perfect would be a data point at each sensor reading.
Example:

  • sensor sends data at 14:13 → plot a point in the time series panel that shows amount of rainfall between 13:13 and 14:13.
  • sensor sends next data at 14:23 → plot a point in the time series panel that shows amount of rainfall between 13:23 and 14:23.
  • and so on

It would be also acceptable to have the amount of rain fall between fixed 10 minutes intervals.
Example:

  • independent from actual sensor readings at 14:10 → plot a point in the time series panel that shows amount of rainfall between 13:10 and 14:10.
  • at 14:20 → plot a point in the time series panel that shows amount of rainfall between 13:20 and 14:20.
  • at 14:30 → plot a point in the time series panel that shows amount of rainfall between 13:30 and 14:30.
  • and so on

I had lengthy discussions with ChatGPT but in the end we turned in circles :see_no_evil:.

All I could achieve is a datapoint each hour.
Example:

  • independent from actual sensor readings at 14:00 → plot a point in the time series panel that shows amount of rainfall between 13:00 and 14:00.
  • at 15:00 → plot a point in the time series panel that shows amount of rainfall between 14:00 and 15:00.
  • at 16:00 → plot a point in the time series panel that shows amount of rainfall between 15:00 and 16:00.
  • and so on

The related query is:
SELECT sum("RainCorrDiff") FROM "coop_garden_calcv" WHERE $timeFilter GROUP BY time(1h) fill(null)

Alternatively I managed to have a point each 10 min (though not related to the time of the actual sensor reading) - but then the graph shows only data from the last hour. No older data are shown :confounded:.
The query is:

SELECT
    SUM("RainCorrDiff") AS "Regenmenge_pro_Stunde"
FROM (SELECT "RainCorrDiff" FROM "coop_garden_calcv" WHERE time >= now() - 1h) 
WHERE time >= now() -1h AND time < now()
GROUP BY time(10m)

InfluxDB version 2.7.10
Grafana version: 11.1.1

Do you have any ideas what would be the correct way to achieve what I want?

Thanks
Christian

2

Hello @cortlieb,
I’m not sure if there is a way to make rolling sums with InfluxQL.
There is a rolling average function:

And then you could multiply it by the amount of points to get a sum…but that would be a rolling window based off point count not time.

However you can do this with Flux:

from(bucket: "example-bucket")
    |> range(start: -1d)
    |> filter(fn: (r) => r._measurement == "coop_garden_calcv")
    |> window(every: 10m, period: 1h)
    |> sum()

Unless @scott or @Jason_Stirnaman knows an influxql workaround.

3

InfluxQL isn’t able to do this. InfluxQL can only aggregate data using “normalized” window boundaries, which aren’t defined by the data itself. For example, if you do GROUP BY time(1h), InfluxQL uses window boundaries on the hour, every hour. In your case, you’re wanting to do aggregation based on the timestamp in the data.

I think this type of query would be possible with Flux, but it would be a tricky query to write and probably not very performant.

This is totally possible… in Flux. InfluxQL doesn’t let you define a window period (the amount of time a window covers) separate from a window interval (how often windows are created). For example, in InfluxQL, when you do GROUP BY time(1h), you get the following window boundaries:

  • 1:00 - 2:00
  • 2:00 - 3:00
  • 3:00 - 4:00
  • etc.

In Flux, you can define both an interval (every) and a period (period) with the window() function. So with window(every: 10m, period: 1h), you’d get the following window boundaries:

  • 1:00 - 2:00
  • 1:10 - 2:10
  • 1:20 - 2:20
  • 1:30 - 2:30
  • etc.

I don’t know that what you’re trying to do is possible with InfluxQL.

4

@cortlieb
For the acceptable solution, I don’t think you need to group by time since you are basically just asking for the raw data, so you could use:

SELECT RainCorrDiff FROM coop_garden_calcv WHERE $timeFilter

To test, I generated some line protocol using this python (sample between 0 and 10 every 10 minutes for 24 hours at 13 minutes past the hour):

from random import randrange

# Monday, 21 October 2024 00:13:00
epoch = 1729469580

i = 0
str = ""
while i < 6 * 24:
    rain = randrange(0,10,1)
    str += f"coop_garden_calcv RainCorrDiff={rain} {epoch}\n"
    # increment 10m
    epoch += 600
    i += 1

print(str)

Which you can see here, you can use the inbuilt grafana total to add all the values for you to see the total over the selected period:

image
image2430×1104 226 KB

If the range of time gets big, you would want to keep the minimum interval to 10m rather than letting grafana choose for you:

image
image1720×472 38.6 KB

Hope this helps,
Thanks,
Tom

5

@cortlieb

I am having the same issue as you. Were you able to get the perfect solution working?

For the acceptable solution I was able to simply do
SELECT sum(<my_data>) FROM <my_table> GROUP BY time(1hr)

But as you mentioned, it only calculates on the hour (and Im fine with the hour boundaries)… not a rolling sum on every data point or smaller interval.

Is this possible with SQL? I am using Influxdb3 which only supports InfluxQL and SQL