To display device positions on a map with history, I first have the positions written to an InfluxDB. I display the data from the InfluxDB with Grafana.
The pivot function must be used for this: The pivot() function in InfluxDB (Flux query language) is used to reshape data by converting a long, narrow table into a wide table. It moves values from a column into multiple columns, making it easier to analyse and visualize time series data.
Example in line 6
from(bucket: "hass")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "device_tracker.iphone")
|> filter(fn: (r) => r["_field"] == "longitude" or r["_field"] == "latitude")
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")Result:
