InfluxDB/Grafana return counter starting at zero

I am monitoring water usage at home and I have an always upward moving counter that represents litres of water used since my source device restarted. I display this on a graph with say 1 days data and it gives a good indication of water usage. The problem is once the counter gets to several thousand litres it's difficult to read the graph as it shows starting as something like 10,123 and end 10,456. I would like to subtract 10,123 from every value on the graph so the value starts at zero.

As an example, this is my query and results. I'd like to subtract 417.388698630137 from every value returned. I would be happy for a solution in either Influx or Grafana. Bonus points if the solution works over a counter reset. :-)

> SELECT mean("waterTotalVolumeMeter") 
FROM "detailed_data"."water" 
WHERE time > now() - 6h 
GROUP BY time(1h) fill(null)

name: water
time                mean
----                ----
1592600400000000000 417.388698630137
1592604000000000000 423.9315642458101
1592607600000000000 432.36805555555554
1592611200000000000 443.77777777777777
1592614800000000000 450.3611111111111
1592618400000000000 504.5763888888889
1592622000000000000 544.8134328358209

Solution

I found a solution that appears to work, I'm not sure if it's the best solution but posting it as it might be useful to someone. This doesn't always return 0 for the first result as there might be some counter increments in the first interval, but this does do what I want. I've returned the original value only just to highlight what it's doing.

EDIT: I have found you need to set fill(previous) to cope with gaps in the data, without this the end result is not accurate and the graph jumps unexpectedly. I've also found it copes well with counter resets which is why we need non_negative_derivative.

SELECT cumulative_sum(non_negative_derivative(mean("waterTotalVolumeMeter"), 1h)), mean("waterTotalVolumeMeter") FROM "detailed_data"."water" WHERE time > now() - 12h GROUP BY time(1h) fill(previous)
name: water
time                cumulative_sum     mean
----                --------------     ----
1592600400000000000 5.993055555555543  416.29008746355686
1592604000000000000 13.93156424581008  423.9315642458101
1592607600000000000 22.368055555555543 432.36805555555554
1592611200000000000 33.77777777777777  443.77777777777777
1592614800000000000 40.361111111111086 450.3611111111111
1592618400000000000 94.57638888888891  504.5763888888889
1592622000000000000 138.69444444444446 548.6944444444445
1592625600000000000 144.27083333333337 554.2708333333334
1592629200000000000 154.91666666666663 564.9166666666666
1592632800000000000 159                569
1592636400000000000 229.09027777777783 639.0902777777778
1592640000000000000 324.88194444444446 734.8819444444445
1592643600000000000 375                785

I also found this is the only method that gives accurate results for a single stat. If we want to show the amount of something consumed in a time interval based on a counter then the logical method is to subtract the min value from max or use the spread function. Neither of these work though for counter resets. To cope with counter resets the only option is to sum up all the positive increments which is what this does. I found 3 ways to use it in a single stat:

Leave query as is and let Grafana do the max

SELECT cumulative_sum(non_negative_derivative(mean("waterTotalVolumeMeter"), 1h)) AS X FROM "detailed_data"."water" WHERE time > now() -24h GROUP BY time(1h) fill(previous)

Do max in influx with original query as subquery

SELECT MAX(X) FROM (SELECT cumulative_sum(non_negative_derivative(mean("waterTotalVolumeMeter"), 1h)) AS X FROM "detailed_data"."water" WHERE time > now() - 24h GROUP BY time(1h) fill(previous))

Use sum in influx like this:

SELECT SUM(X) FROM (SELECT non_negative_derivative(mean("waterTotalVolumeMeter"), 1h) AS X FROM "detailed_data"."water" WHERE time > now() - 24h GROUP BY time(1h) fill(previous))