InfluxDB set first data point to 0

Grafana InfluxDB
1

Hello,
I have the following chart:

image
image1237Ă—446 17.2 KB

Now what I would like to do is to basically set the very first visible data point to 0 and adjust all the others so the last data point is the actual last value - the the actual start value.

This is my usecase:
I have a sensor which sends the total amount of rain it registered in millimeters. Now I would like to see how much it has rained over a given time period and how the speed changed, without having to subtract the start value in my head.

2

Welcome to the Grafana forum.

For the case you described, are you always measuring the rainfall over the same period (e.g. midnight to midnight)? If so, then I think just having a stat panel with sparkline showing TOTAL amount the mm of rain would be a more logical panel (rather than trying re-engineer the time series panel). Do you feel that would meet your needs?

image
image1074Ă—612 45.6 KB

3

no, it is the total since reboot, so theoretically always increasing. I don’t want a single stat but a time series showing how fast it increased. I would like to be able to tell my dashboard to show me the amount of rain since time x and how it changed over that range.

4

You have counter and you want to have rate of change - typical use case for DERIVATE() or better for NON_NEGATIVE_DERIVATIVE() (because negative rate when sensor is restared doesn’t make sense) influxDB function.

5

yeah tried that, but it takes a time parameter. I can’t figure out how to put the entire range in there.

6

Also, have you tried the Difference transformation?

image
image1075Ă—505 46.6 KB

7

image
image1422Ă—841 43.9 KB

shows only a line that stays at 0

8

No, you don’t need a time parameter. Linked InfluxDB manual is your friend:

The unit argument is an integer followed by a duration literal and it is optional. If the query does not specify the unit , the unit defaults to the GROUP BY time()

Query:

SELECT
 NON_NEGATIVE_DERIVATIVE(counter_field)
FROM measurement
WHERE $timeFilter
GROUP BY time($__interval)

→ result is timeseries

If you are not familiar with used macros/variabes, then doc is your good friend again: Add and manage variables | Grafana documentation

9

image
image1370Ă—437 29.6 KB

not really what I want, I want the first value to be set to 0 and then increase according to the data

image
image1238Ă—873 49.6 KB

(basically offsetiting the entire y axis by -(firstValue))

10

You have change of rate, so now apply another function CUMULATIVE_SUM() in the subquery and you will have desired result.

You can’t stick with the approach of “offsetting the entire y axis by -(firstValue)”, you need to hav more math approach to have desired result.

11

thank you, this works. However it is not very accurate.
The difference in the actual data is 3.9, but the cumulative sum of the rate is only 2.02

What could be the cause of that?

This is the query now:

SELECT cumulative_sum(non_negative_derivative(mean("value"))) *0.48352 FROM "bed_wall_precipitation_counts" WHERE $timeFilter GROUP BY time($__interval) fill(none)
12

what you require may be achieved with the influx db DIFFERENCE function.

The grafana difference function is only the difference between the last and first value in the array.

what you require is the difference value between each array value and this may be done using the influx db DIFFERENCE function.

the derivative will not work well in your case as the time step is not the same and the values have very small incremental change hence the cumulative error you are getting/seeing.

1 Like