Using InfluxDB subquery to subtract values

Question

I have a Influx database that is getting filled with values. These values are presented by Grafana. What I need is to get the actual values depending on the selected time interval.

Currently I have the following query for a single metric:

SELECT mean("value") FROM "table" WHERE $timeFilter GROUP BY time($interval) fill(null)

What I want is to subtract the lowest value from that interval, so it only counts the values from within that interval. So the graph needs to start at zero. To get the lowest value from that interval I use:

SELECT min("value") FROM "table" WHERE $timeFilter 

So I thought combining those two (with a subquery) like this should work:

SELECT mean("value") - (SELECT min("value") FROM "table" WHERE $timeFilter) FROM "table" WHERE $timeFilter GROUP BY time($interval) fill(null)

Unfortunately this doesnt work. The query is not accepted as a subquery.

Answer 1

This is possible using InfluxDB's built-in functions:

SELECT cumulative_sum(difference((mean("value")))) FROM "table" WHERE $timeFilter GROUP BY time($interval) fill(null)

This takes the difference between consecutive data points (ignoring the absolute value), and cumulatively sums this over the selected time range.

I'm not entirely sure, but this query requires an additional mean() associated with the GROUP BY clause. Perhaps it's better to use max or first instead, depending on your needs.

Update

Alternatively, you can indeed use subqueries and GROUP BY to achieve this, but this way you can only get time ranges that can be represented by GROUP BY (e.g. from 14:13 to 15:16 on July 6 2010 is more difficult).

SELECT (energy_live-energy_fill) as Energy FROM
  (SELECT first(value) as energy_fill from energyv3 WHERE $timeFilter GROUP BY time(1d)),
  (SELECT first(value) as energy_live from energyv3 WHERE $timeFilter GROUP BY time($__interval))
fill(previous)