How can I plot the difference value(now) - value(24h ago) in Flux?

Hello,

I have the following Flux query that plots the value read from a sensor over time:

from(bucket: "adeunis")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["topic"] == "adeunis")
  |> filter(fn: (r) => r["_field"] == "temperatures_0_value")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

What I would like to do is to write a query that returns the difference between the last read value and a value from yesterday (let’s say yesterday @ 8.00 PM).

Could this be possible using Flux, or should I write a script?

Pretty sure this recipe will work for you.

https://docs.influxdata.com/influxdb/v2.0/query-data/common-queries/compare-values/

(Untested , but this should help get you started)

CurrentRange = from(bucket: "adeunis")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["topic"] == "adeunis")
  |> filter(fn: (r) => r["_field"] == "temperatures_0_value")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")


PriorRange = from(bucket: "adeunis")
  |> range(start: v.timeRangeStart - 24h, stop: v.timeRangeStop - 24h)
  |> filter(fn: (r) => r["_measurement"] == "mqtt_consumer")
  |> filter(fn: (r) => r["topic"] == "adeunis")
  |> filter(fn: (r) => r["_field"] == "temperatures_0_value")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")


# then apply the join per the example. Something similar to this but will need tweaking probably 


join(tables: {curr : CurrentRange, prior: PriorRange}, on: ["topic"])
  |> map(fn: (r) => ({r with deviation: r._value_curr - r._value_prior}))


You will probably need to play with the join (eg on topic, and the tabling naming, and/or the map that calculates the difference

I will try this. Thanks!

Apparently, Flux has the difference() function. This, used with an agreggateWindow(every 24h), works pretty well for me.

https://docs.influxdata.com/influxdb/cloud/query-data/get-started/query-influxdb/
https://docs.influxdata.com/flux/v0.x/stdlib/universe/difference/

1 Like

Thanks for posting the final solution you ended up with :+1: