12

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.

Nhz
  • 131
  • 1
  • 6
  • What version of Influx are you using? In principle in 1.2 should be possible to do something like that. – Pigueiras Apr 04 '17 at 19:32
  • I am using InfluxDB shell version: 1.2.0. I know subquerys are possible, but as far as the documentation goes only in the 'FROM' section. The documentation does not give examples of other types of subquery's. – Nhz Apr 05 '17 at 08:44
  • 2
    You are completely right, it looks like it is not possible. I thought in some hack doing templating with grafana, but it doesn't work because it shows the timestamp instead of the `min("count")`. I guess the best you can get is to calculate the min and the substract the constant from the query :( – Pigueiras Apr 05 '17 at 19:46
  • 1
    Thanks for the confirmation. What do you mean with 'subtract the constant'? Could you explain how this works? – Nhz Apr 06 '17 at 07:29
  • 1
    I mean: `SELECT mean("value") - 123 FROM ...` – Pigueiras Apr 06 '17 at 09:17
  • Yeah, well that's not an option. These values can change every second, and per time selection the 'min value' is different. – Nhz Apr 06 '17 at 13:04
  • Unfortunately, [subqueries can only be used in the FROM clause](https://docs.influxdata.com/influxdb/v1.8/query_language/explore-data/#subqueries) of another query, but not as a value. – Dan Dascalescu Aug 24 '20 at 23:51

1 Answers1

2

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)
Tim
  • 1,466
  • 16
  • 24