Sum daily rain - Sensor zeros at 0700

1

Hi

I have the followin goal: I would like to see for rain

  1. daily max value and
  2. its cummulative sum in the selected interval

Both of which I figured out how to query the database and display it nicely in a graph.

Problem: The sensor I use zeros every mornig at 0700 (Homematic IP Weather Sensor Plus). Situation: It rains 10mm on day x and stops at 2200. Max value on day x is 10mm. Then on day y it does not rain at all. But since the sensor zeros on 0700 on day y I get max value 10mm (from day x) for day y (instead of 0mm).

Changing the programming of the device is not possible with my setup. So is there possibility to calculate the correct amount of rain with a grafana query?

I use InfluxDB.

Thanks
akrea

2

I would use something like node-red to monitor the sensor and generate a running total and put that into influx. Then you should be able to do what you want as you can subtract the value at the start of the day.

3

It looks like a task for SUM of NON_NEGATIVE_DERIVATIVE(value). Of course you will need to use InfluxDB subqueries. It will be more complicated math/query than simple MAX aggregation, but it will give you correct values.

4

Thanks for the input - seems an interesting tool. Yet for now I would like to stick to what I already have on my server.

5

Thank you for your reply. Unfortunately I couldn’t figure how NON_NEGATIVE_DERIVATVE(value) could help me out here. Let me put the query I’m going for in text here:

“last value at between 07:00:00 on ‘day x’ and 06:59:59 on ‘day x+1’ (zero if none)” - “value at 00:00:01 on day x+1 (zero if none)” + “value at 24:00:00 on ‘day x+1’” = “Total amount of rain”

Of course this calculation has to be done for every day within the range I select.

Unfortunately I already fail at selecting the first of the three values I need as I cannot figure out how to query the values for every single day in a given range. Can you help me out in replacing “everyday” in the syntax below with the correct code? I couldn’t find anything on the net no this particular problem.

SELECT last("value") FROM "mm" WHERE time>'everydayT07:00:00Z' AND time<'everyday+1T06:59:59Z' GROUP BY time(1d)

Again: thanks for your help

6

Actually, NON_NEGATIVE_DIFFERENCE is the right function.

1.) NON_NEGATIVE_DIFFERENCE returns the non-negative result of subtraction between subsequent field values. Non-negative results of subtraction include positive differences and differences that equal zero:

SELECT NON_NEGATIVE_DIFFERENCE("value") AS nnd
FROM "mm" 
WHERE $timeFilter

2.) Sum result from 1.) per day with subquery:

SELECT SUM(nnd) 
FROM (
  SELECT NON_NEGATIVE_DIFFERENCE("value") AS nnd
  FROM "mm" 
  WHERE $timeFilter
) time(1d)

Test, verify and improve it (there can be some query syntax error).

7

Hi

Thanks for the help. 1. is solved. 2. remains:

I was going for
SELECT CUMULATIVE_SUM(nnd) FROM (SELECT NON_NEGATIVE_DIFFERENCE("value") AS nnd FROM "mm" WHERE $timeFilter) GROUP BY time(1d))

but i always get the error
aggregate function required inside the call to cumulative_sum

I don’t know how to approach that thing as several alterations (putting sub queries within brackets of cumulative_sum) didn’t work. Can you help out again?

Thank you

8

What is the reason to use CUMULATIVE_SUM and not just SUM? Try to add time aggregation to inner query, which will be the same as data granularity.

Could you use better code formatting for your queries. It is hard to read and understand + I think there is syntax error - why is the last bracket is there?

9

I thought the CUMILATIVE_SUM() would be the right function to achieve goal 2…

Below your function with slight alterations with which I could achieve goal 1.

SELECT SUM(nnd) 
  FROM (
  SELECT NON_NEGATIVE_DIFFERENCE("value")
    AS nnd FROM "mm" 
    WHERE $timeFilter
  ) time(1d)

this gives me the bar chart I was looking for:

barchart
barchart751×314 10.9 KB

For my second goal I would like to add up the calculated values of the querry above to give me a line chart where I can see the cumulative sum of the selected period.

I had some obvious mistakes in the formula of the last post. Sorry for that! Below the “real thing” which I tried:

SELECT CUMULATIVE_SUM(nnd) 
FROM (
  SELECT SUM(nnd)
  FROM (
    SELECT NON_NEGATIVE_DIFFERENCE("value") 
    AS nnd FROM "mm"
    WHERE $timeFilter
  ) GROUP BY time(1d)
) GROUP BY time(1d)

tihs formula gives me still the same error: aggregate function required inside the call to cumulative_sum

Hope this clarifies things a bit. Again thanks for your help

10

I don’t know :thinking:. https://community.influxdata.com/ will be better forum to ask, because it is InfluxDB query problem and not real Grafana issue.

11

Hi

The solution is

SELECT 
	CUMULATIVE_SUM(SUM(nnd)) 
FROM (
	SELECT NON_NEGATIVE_DIFFERENCE("value") AS nnd FROM "mm"
	WHERE $timeFilter
) GROUP BY time(1d)

See this post!

Thanks for your help anyway :+1:

1 Like