Get Values relative to zero

grafana
influxdb

#1

Hello all, I need help with a query, that I don’t know if it’s possible to solve. I have a series of measurements that contain the value of the accumulated total of KWh of my home. I use this to display in Grafana the data! The problem is that when I set the time period in grafana I get the KWh, but I would like to have it beginning in “Zero” for SELECT. I tried:

SELECT "kwh" - min("kwh") FROM "power" WHERE ("deviceId" = 'main') AND $timeFilter

But this only returns one record, and with GROUP BY the result is not the intended, I want to see the graph growing, but starting on zero.

Thank you all
Best Regards


#2

I don’t see how to simply get the min value of the whole time range and subtract by that value wih an influxql query.

One way to get the result you want is to use subqueries. First calculate the difference between the values, then do a cumulative sum.

I got the idea here:


#3

Thank you @samaust it worked 100% :slightly_smiling_face:

This is my final query in Grafana, it may be useful to someone else!

SELECT cumulative_sum(difference((min("kwh")))) FROM "power" WHERE ("deviceId" =~ /^$device$/) AND $timeFilter GROUP BY time(1s)

Thank you