InfluxDB 2.0 (Flux)

[viktak]

Still loving this plugin! :)

I just migrated my setup to InfluxDB 2.0, which is using Flux as the query language. I am completely new to this, and was wondering if you have an example query (for this plugin) I could use as a starting point.

thank you

[Alkarex]

No sorry, this is not something we have used (yet) :-) We are mostly using PostgreSQL + Timescale + PostGIS at the moment

[viktak]

ok, no problem, I'll figure it out and will leave a sample here for others to see. If you could leave this open and maybe assign it to me...? thanks!

[viktak]

I have figured it out over the week-end. In fact I found multiple ways of doing it. This one is the most concise one, that works:

from(bucket: "home_assistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "device_tracker.mate10")
  |> filter(fn: (r) => r["_field"] == "gps_accuracy" or r["_field"] == "latitude" or r["_field"] == "longitude" or r["_field"] == "velocity")

|> pivot(columnKey: ["_field"], rowKey: ["_time"], valueColumn: "_value")

|> duplicate(column: "latitude", as: "lat")
|> duplicate(column: "longitude", as: "lon")
|> duplicate(column: "_time", as: "tooltip")
|> duplicate(column: "velocity", as: "popup")

|> map(fn: (r) => ({ r with popup: "Speed: " + string(v:r.popup) + " km/h" }))

|> keep(columns: ["_time", "tooltip", "lat", "lon", "popup"])

The only problem with it is that in the tooltip (hover over) it shows the time in epoch format. Is there a way to format it to a more civilized way? :) Once I can figure it out (someone please help!), I will update it here so that you can perhaps add it to the documentation. I will also provide a screenshot.

[viktak]

It seems to me that the time formatting comes from this plugin: when I change the visualization to a simple table, the tooltip column displays time the way I would like it to do it, i.e. 2021-03-07 10:51:56. The moment I change visualization to Track Map the tooltip shows epoch format, like this:

Is there any processing done in your code on the data before it gets displayed as a tooltip?

[viktak]

One more observation: When I want to display the timestamp in the popup field, it works, well, kind of. the same column redirected to the popup field displays like this:

This is still not nice, but definitely more readable than the epoch format.

Ideally, I would like to be able to format it, say the way it works in c++'s printf

[steve-burke]

I raised https://github.com/alexandrainst/alexandra-trackmap-panel/issues/52 in regard to the inability to display readable time using InfluxDB 1.8 and InfluxQL. I couldn't even get the readable timestamp in the popup field.

So I tried again using Flux, and again I couldn't get the readable popup timestamp, which I therefore suppose requires using InfluxDB 2.0.

However I was able to fix the issue using the following query:

import "strings"
import "date"
from(bucket: "monitor/autogen")
  |> range(start: v.timeRangeStart)
  |> filter(fn: (r) => r._measurement == "locations")
  |> filter(fn: (r) => (r._field == "latitude" or r._field == "longitude"))
  |> filter(fn: (r) => r.entity_id == "dads_iphone")

  |> pivot(columnKey: ["_field"], rowKey: ["_time"], valueColumn: "_value")

 |> map(fn: (r) => ({ 
    latitude: r.latitude,
    longitude: r.longitude,
    tooltip: strings.joinStr(arr: 
    [strings.joinStr(arr: 
    [string(v:date.year(t: r._time)), 
    strings.substring(v: strings.joinStr(arr: ["0", string(v: date.month(t: r._time))],v:""), start: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.month(t: r._time))],v:""))-2, end: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.month(t: r._time))],v:""))), 
    strings.substring(v: strings.joinStr(arr: ["0", string(v: date.monthDay(t: r._time))],v:""), start: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.monthDay(t: r._time))],v:""))-2, end: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.monthDay(t: r._time))],v:"")))
    ], 
    v:"-"),
    strings.joinStr(arr: [
    strings.substring(v: strings.joinStr(arr: ["0", string(v: date.hour(t: r._time))],v:""), start: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.hour(t: r._time))],v:""))-2, end: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.hour(t: r._time))],v:""))), 
    strings.substring(v: strings.joinStr(arr: ["0", string(v: date.minute(t: r._time))],v:""), start: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.minute(t: r._time))],v:""))-2, end: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.minute(t: r._time))],v:""))), 
    strings.substring(v: strings.joinStr(arr: ["0", string(v: date.second(t: r._time))],v:""), start: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.second(t: r._time))],v:""))-2, end: strings.strlen(v: strings.joinStr(arr: ["0", string(v: date.second(t: r._time))],v:"")))
    ], v:":")],
    v: " ")
    }))

This substantially slows data retrieval, due to all the string formatting code - if you are not bothered about the leading zeroes it runs much faster. Maybe someone more knowledgeable knows a more efficient method.

[steve-burke]

This is substantially faster:

import "strings"
import "date"
leadzero = (n) => {
    txt = strings.joinStr(arr: ["0", string(v: n)], v: "")
    l = strings.strlen(v: txt)
    return strings.substring(v: txt, start: l-2, end: l)
    }

from(bucket: "monitor/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "locations")
  |> filter(fn: (r) => (r._field == "latitude" or r._field == "longitude"))
  |> filter(fn: (r) => r.entity_id == "dads_iphone")

  |> pivot(columnKey: ["_field"], rowKey: ["_time"], valueColumn: "_value")

   |> map(fn: (r) => ({ 
    latitude: r.latitude,
    longitude: r.longitude,
    tooltip: 
    strings.joinStr(arr: [
    strings.joinStr(arr: [
    string(v:date.year(t: r._time)),
    leadzero(n: date.month(t: r._time)),
    leadzero(n: date.monthDay(t: r._time))
    ], v: "-"),
    strings.joinStr(arr: [
    leadzero(n: date.hour(t: r._time)),
    leadzero(n: date.minute(t: r._time)),
    leadzero(n: date.second(t: r._time))
    ], v: ":")
    ], v: " ")
    }))

[viktak]

Awesome, thanks for it, @steve-burke , it works like a charm! I don't notice any significant loss in speed. However, the proper solution would be if the component supported it.

[steve-burke]

I agree @viktak, although this issue and your post have resulted in my learning about Flux, which was interesting.

[steve-burke]

Still learning...

The following is more efficient. However, none of these solutions display local time; they all show UTC. I can't find a way of fixing this in Flux.

import "strings"

from(bucket: "monitor/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "locations")
  |> filter(fn: (r) => (r._field == "latitude" or r._field == "longitude"))
  |> filter(fn: (r) => r.entity_id == "dads_iphone")

  |> pivot(columnKey: ["_field"], rowKey: ["_time"], valueColumn: "_value")

   |> map(fn: (r) => ({ 
    latitude: r.latitude,
    longitude: r.longitude,
    tooltip: 
    strings.substring(v: 
    strings.replace(v:
    string(v: time( v: r._time))
    , t: "T", u: " ", i: 1)
    , start: 0, end: 19)
    }))