[InfluxQL] Selecting value and time since last value

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

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)

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"
)

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://v2.docs.influxdata.com/v2.0/reference/flux/

Thank you!

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

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: https://github.com/grafana/influxdb-flux-datasource/issues/124