Flux Pivot() to Value vs Hours grouped by Day

InfluxDB 2 Dashboards
1

Hi! I’m using Influx 1.7.0 with Chronograf to use the flux editor.

My goal is to display a graph similar to the below in Grafana. This graph (I think), needs transforming beyond InfluxQL’s abilities. A heatmap comes close but is not quite right. Hence: use Flux.

The data are values (0-4 on the y-axis) across many days (one per line in the graph), with hour-of-day on the x-axis. The original timeseries data is just a single measurement. In pyplot, Excel, etc, a “Pivot” is used to produce a dataset as follows:

index
index1402×1106 161 KB

I’m here with the flux query. I think the rowKey should be “time-of-day” and the columnKey “day” or similar.

from(bucket: “iotawatt/autogen”)
|> range(start:-3d)
|> filter(fn: (r) => r._measurement == “Net” and (r._field == “value”))
|> pivot(
rowKey:[“_time”],
columnKey: [“_field”],
valueColumn: “_value”
)

My questions:

  1. How do I use pivot() to perform this query? (Or something else)
  2. Once that’s done, what’s the best approach to graphing this in grafana? (Perhaps this is a question for elsewhere.)

Thanks, Brett

2

Hi! Could we get a bit more info on your schema? This query output would be helpful:

from(bucket: “iotawatt/autogen”)
|> range(start:-3d)
|> filter(fn: (r) => r._measurement == “Net” and (r._field == “value”))
|> limit(n: 10)

Adjust the limit n value to whatever makes sense to get a feel for the data. If posting the data itself it a problem, you may also just post the column schemas output by the flux query.