InfluxDB: Field name when using "elapsed

[dert94]

• What Grafana version and what operating system are you using?
  9.4.7

• What are you trying to achieve?
  I want to use a calculated field in an expression

• How are you trying to achieve it?
  I have two queries

from(bucket: "ioBroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_HOUSE_POW")
  |> filter(fn: (r) => r["_field"] == "value")
  |> elapsed(unit: 1s)
  |> map(fn: (r) => ({ r with _value: (float(v: r._value) / 1000.0) * (float(v: r.elapsed) / 3600.0) })) // formula 
  |> sum()
  |> yield(name: "value")

and

from(bucket: "ioBroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_GRID_POW")
  |> filter(fn: (r) => r["_field"] == "value")
  |> elapsed(unit: 1s)
  |> map(
    fn: (r) => ({r with
      _value: if r._value <= 0.0 then
        0.0
      else
        (float(v: r._value)/1000.0) * (float(v: r.elapsed) / 3600.0),
      }),
    )
  |>sum()
  |> yield(name: "value")

I would like to subtract the result of the two queries from each other with a transformation.

But the field names also contain the time span. Thus the calculation works only within the time span

[grant2]

Welcome @dert94 to the Grafana forum.

How about (in InfluxDB) doing a join and then doing the mathematical calculation in the joined table? I would work this out in Influx Data Explorer (with view Raw Data enabled) and once you have it working there, paste it into Grafana.

[dert94]

Hey,

I am not quite sure how you mean it. Can you explain it a little bit more?

In Influx I get a similar result with and without “elapsed”. Only without the calculation

In this test, I noticed that sum() causes the same thing.

Table View in Grafana

[grant2]

Hi @dert94

You would have two tables (t1 and t2, representing your two queries) and then do the math in the joined table. Something like this should work:

t1 = from(bucket: "ioBroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_HOUSE_POW")
  |> filter(fn: (r) => r["_field"] == "value")
  |> elapsed(unit: 1s)
  |> map(fn: (r) => ({ r with _value: (float(v: r._value) / 1000.0) * (float(v: r.elapsed) / 3600.0) })) // formula 
  |> sum()
  |> drop(columns: ["_start", "_stop"])

t2 = from(bucket: "ioBroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_GRID_POW")
  |> filter(fn: (r) => r["_field"] == "value")
  |> elapsed(unit: 1s)
  |> map(
    fn: (r) => ({r with
      _value: if r._value <= 0.0 then
        0.0
      else
        (float(v: r._value)/1000.0) * (float(v: r.elapsed) / 3600.0),
      }),
    )
  |> sum()
  |> drop(columns: ["_start", "_stop"])

join(
  tables: {t1: t1, t2: t2},
  on: ["_time", "_measurement"],
  method: "inner"
)
  |> map(fn: (r) => ({_time: r._time, _measurement: r._measurement, _difference: r._value_t1 - r._value_t2}))
  |> yield(name: "difference")

[dert94]

Hi,

I have tried your query. But after the join I get no data.
T1 and T2 work well when I try them separately.
The join returns nothing.
You are using the “_time” column for the join. But in the output of t1 and t2 there is no “-time” column.

But I was able to solve it in a different way.
The field name is still wrong but it currently works.

import "contrib/tomhollingworth/events"
from(bucket: "ioBroker")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "senec.0.ENERGY.GUI_HOUSE_POW" or r["_measurement"] == "senec.0.ENERGY.GUI_GRID_POW")
  |> filter(fn: (r) => r["_field"] == "value")
  |> events.duration(unit: 1s)
  |> map(
    fn: (r) => ({r with
      _value: if r._value <= 0.0 then
        0.0
      else
        (float(v: r._value)/1000.0) * (float(v: r.duration) / 3600.0),
      }),
    )
    |> sum()
  |> pivot(rowKey: ["_field"], columnKey: ["_measurement"], valueColumn: "_value")
  |> map(fn: (r) => ({ r with _value: r["senec.0.ENERGY.GUI_HOUSE_POW"]-r["senec.0.ENERGY.GUI_GRID_POW"] })) 
  |> drop(columns: ["senec.0.ENERGY.GUI_HOUSE_POW","senec.0.ENERGY.GUI_GRID_POW"])