Transitioning from InfluxDB 1.x InfluxQL to 2.x Flux query language

Grafana
1

I’m looking to transition my InfluxDB and Grafana setup from my Home Assistant plugins to a cloud solution. The primary dashboard goal is for power monitoring.

With my current setup using InfluxQL it works easily as the GUI helps me.
graf-db1-query

Trying to understand the Flux language to do the same has me stumped. I don’t know if my error is in the query or the way Grafana uses the data.
graf-db2-query

Basically I want to take the Watts recorded, group in 10s slots, and divide by 3600 to convert to Watt Hours.

Any help is appreciated. Thanks in advance.

2

Are you getting any error with this query? Seems reasonably written…

One thing: you don’t need the ‘r with…’ when you map. I would ditch all columns except the ones needed rather than add even more columns to my table. So more along the lines of 'map(fn: (r) => ({_time = r._time, _value = r._value / 3600.0}, _field: “WattHour”) ’

If you’re specific about the 10s, you should specify them as a fixed window, rather than let the view set it dynamically, so: ‘every: 10s’

And finally I am doubtful about your maths to get Wh as a unit by simply summing your data and then dividing by 3600. It will very much depend on how your data is sampled. Firstly, if you have gaps in your data, your cumsum will fall well short. Secondly, dividing by 3600 and not taking into account the aggregate window will simply be wrong. If you divide by 3600 your aggregate window must be 1 second.

Try this instead:

myWindow = 600.0 //unit is seconds !!
myDuration = duration(v:(string(v:myWindow)+"s"))
from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "W")
  |> filter(fn: (r) => r["entity_id"] == "efergy815686")
  |> filter(fn: (r) => r["_field"] == "value")
  |> aggregateWindow(every: myDuration, fn: mean)
  |> map(fn: (r) => ({ _value: r._value * myWindow / 3600.0 , _time:r._time, _field:"WattHour" }))
  |> cumulativeSum(columns: ["_value"])

You can set the aggregate window in the ‘myWindow’ variable. Make sure it is larger than your guaranteed minimum sample rate if you don’t want to have errors due to gaps in your data. At the same time, if you make it too large, then you will loose accuracy in your cumsum due to reduced granularity. Plot the data with points so you can see the effects of the chosen window size.

An alternative for a fixed window size is to use v.windowPeriod but then you will need:

  1. some scripting to verify what window was set automatically by Grafana and use that to adjust your calculation
  2. still make sure you set the minimum window size larger or equal to your sampling rate

I am making a big fuss of the minimum sampling rate and gaps because my data is sampled ‘on change’ and with a large minimum frequency to preserve bandwidth. Flux at this stage does not offer a function to fill in the gaps (ie. interpolate: Proposal – Time Interpolation · Issue #2428 · influxdata/flux · GitHub) so you have to be cautious if you sample the way I do.

Good luck!

10min window and 1h windows - similar results but different resolution:

image
image1825×1296 206 KB

image
image1822×1283 197 KB

3

using influxdb oss 1.8 grafana 7.1 with native FLUX datasource range does not behave as expected.

attempt 1. |> range($range) - properly respects the start and stop from grafana date/time picker
attempt 2. |> range(start: v.timeRangeStart, stop: v.timeRangeStop) - results in error: undefined identifier ““v””" "
what i REALLY hope to achieve it to apply some “day offset” to my timeRangeStart and timeRangeStop (End …seen confusing references in docs)
use case:
time picker is today so far (now/d - now) - assume its 11AM
query 1 grabs some current day data
query 2 grabs data from some offset (1d, 2d, 3d etc) from its start of day until its 11AM

Ideally, i would think |> range($range - 2d) should work, but error is response:"error type error 2:53-2:82: time != duration "

is there a way to apply duration offset from grafana “human friendly” timeRangeStart / timeRangeStop so its converts to proper time range when FLUX query is served?

IF i use the built in “Time Shift” it applies to ALL queries

4

Hi @quantenabler,

Yes, I’ve seen some confusion too:

  1. the v.timeRangeStop is indeed shown as v.timeRangeEnd in the manual… I guess they changed it. In my OSS v7.1 it luckily made some auto-suggestion which came up as …Stop.

image

  1. the $range variable is an interesting one. I had to use $range in Grafana 6.5, but once upgraded to 7.1 it went to v.timeRangeStart and v.timeRangeStop… I suggest you have a good look at the Query Inspector to see how your time variables are translated before sent to Influx.

In my case:
image
Results in:
image

I believe that is all you can do with Grafana’s global variables. You can define your own variables, supposedly with Flux - but I have not had any success with using Flux for variables in Grafana 7.1 (it was fine in 6.5). So in the end you’ll have to do your time offsets in the Flux query and let the backend deal with it.

For offsetting time, Flux has the following ‘experimental’ functions: https://v2.docs.influxdata.com/v2.0/reference/flux/stdlib/experimental/addduration/

Note:

  1. don’t forget to add import “experimental” at the start of your query.
  2. don’t use the ‘subDuration’ function - that doesnt seem to work. Instead use the ‘addDuration’ with a negative duration.

So for example:

import "experimental"
OffsetStart = experimental.addDuration(d: -48h,  to: now())
OffsetStop = experimental.addDuration(d: -24h,  to: now())

Now, you asked for specifically 11am - not sure why, and you didn’t specify if it is UTC or not. Let’s assume you meant UTC, you would then need to do something like this:

import "experimental"
import "date"
OffsetStart = experimental.addDuration(d: -37h,  to: date.truncate(t: now(), unit: 1d))
OffsetStop = experimental.addDuration(d: -13h,  to: date.truncate(t: now(), unit: 1d))

from(bucket: "yourbucket")
|> range(start: OffsetStart, stop: OffsetStop)

As you can see, I rounded now() to 1day precision - which gives a midnight timestamp, and I then added an offset of -48h + 11h for the start time.

Please note, I am using InfluxdB v2, not 1.8.

Good luck.

5

Thank you very much!

Getting the myWindow variable sorted this out. The Efergy Sensors take a reading every second hence my need to use 3600 seconds per hour. This graph now matches the Efergy Sensors daily use (but now I can look at part days, multiple days, or last ‘x’ hours).

6

would anyone know how would this be in Flux ?

SELECT mean(“usage_idle”) *-1 +100 FROM “cpu” WHERE (“host” =~ /^$hostname$/) AND $timeFilter GROUP BY time($__interval) fill(null)