[InfluxQL] Selecting value and time since last value

InfluxDB 2 Dashboards
1

Hello,

Is there a way to select value of each measurement and time since previous measurement in single SELECT statement?

From what I see, difference method is perfect for that, however, following statement:

SELECT "value", elapsed("value", 1s) FROM ...

produces ERR: mixing aggregate and non-aggregate queries is not supportederror.

This is with InfluxDB 1.7.10.

1 Like
2

Hello @matejdro,
I apologize for the delay. I think you’ll have better luck with Flux, InfluxQL feels quite limited by comparison. Here is how I might look at time elapsed between two measurements, “cats” and “dogs”. I simplified this example and dummy data set by filtering for only two values from two separate measurements:

from(bucket: "animals")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "cats" or r["_measurement"] == "dogs")
  |> filter(fn: (r) => r["type"] == "calico" or r["type"] == "pom")
  |> filter(fn: (r) => r["_field"] == "adult")
  |> filter(fn: (r) => r["shelter"] == "A")
  |> limit(n:2)

Screen Shot 2020-07-15 at 12.50.58 PM
Screen Shot 2020-07-15 at 12.50.58 PM2730Ă—554 34.7 KB 

from(bucket: "animals")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "cats" or r["_measurement"] == "dogs")
  |> filter(fn: (r) => r["type"] == "calico" or r["type"] == "pom")
  |> filter(fn: (r) => r["_field"] == "adult")
  |> filter(fn: (r) => r["shelter"] == "A")
  |> limit(n:2)
  |> elapsed(
  unit: 1s,
  timeColumn: "_time",
  columnName: "elapsed"
)

Screen Shot 2020-07-15 at 12.51.09 PM
Screen Shot 2020-07-15 at 12.51.09 PM2494Ă—406 21.2 KB

Is this what you were looking to achieve? If not can you please provide me with example input data and expected output data? Are you familiar with what Flux is? https://docs.influxdata.com/flux/v0.x/

Thank you!

P.S. You can enable Flux in v1.8+.

3

Thanks for your answer. Will check out as soon as I can get my hands on Flux.

It appears that Grafana does not work with Flux yet for Influx 1.X: No connection details for Influx 1.7.X · Issue #124 · grafana/influxdb-flux-datasource · GitHub