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

daily max value and

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

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.

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)

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

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?

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?

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