So it gets a bit more complicated than just adding a time offset. Although my first attempt (above) worked well, it did not consistently render with the same column width. The column width is calculated dynamically, see @torkel 's comment here: Chart with custom bar width for each time intervall
So in order to avoid overlapping columns you should not only offset the series’ timestamps, you also should add ‘dummy’ zeros in each series. In my flux query, the data table ends up looking like this:
and the graph then like this:
Note:
- I have added a 3rd timestamp with zero for both series, it gives a bettter ‘grouped’ feeling.
- I also ended doing a time shift to align the group nicely above the X-axis label (see myOffset - the value probably changes if you are in a different timezone).
- In flux, you can do this by creating 3 different tables with conditional mapping and then applying a union() function. Maybe someone knows a more efficient query, but it worked for me.
import “experimental”
import “date”myOffset = -14h
myStart = date.truncate(t: experimental.addDuration(d:-24h, to: v.timeRangeStart), unit: 1d)
myStop = date.truncate(t: experimental.addDuration(d: 0h, to: v.timeRangeStop), unit: 1d)Table1 = from(bucket: “R757”)
|> range(start: myStart, stop: myStop)
|> filter(fn: (r) => r[“_measurement”] == “Counters” or r[“_measurement”] == “MMD”)
|> filter(fn: (r) => r[“name”] == “OpHour” or r[“name”] == “CountEnergAct”)
|> aggregateWindow(every: 1d, fn: last)
|> difference()
|> map(fn: (r) => ({
_time: if (r.name == “OpHour”) then experimental.addDuration(d: 8h, to: r._time) else r._time,
_value: r._value,
_field: if (r.name == “OpHour”) then “Operating Hours” else “Active Energy”}))Table2 = from(bucket: “R757”)
|> range(start: myStart, stop: myStop)
|> filter(fn: (r) => r[“_measurement”] == “Counters” or r[“_measurement”] == “MMD”)
|> filter(fn: (r) => r[“name”] == “OpHour” or r[“name”] == “CountEnergAct”)
|> aggregateWindow(every: 1d, fn: last)
|> difference()
|> map(fn: (r) => ({
_time: if (r.name == “OpHour”) then r._time else experimental.addDuration(d: 8h, to: r._time),
_value: 0.0,
_field: if (r.name == “OpHour”) then “Operating Hours” else “Active Energy”}))Dummies = from(bucket: “R757”)
|> range(start: myStart, stop: myStop)
|> filter(fn: (r) => r[“_measurement”] == “Counters” or r[“_measurement”] == “MMD”)
|> filter(fn: (r) => r[“name”] == “OpHour” or r[“name”] == “CountEnergAct”)
|> aggregateWindow(every: 1d, fn: last)
|> map(fn: (r) => ({
_time: experimental.addDuration(d: 16h, to: r._time),
_value: 0.0,
_field: if (r.name == “OpHour”) then “Operating Hours” else “Active Energy”}))UnionTable= union(
tables: [Table1, Table2, Dummies])
|> sort(columns: [“_time”])
|> map(fn: (r) => ({_time: experimental.addDuration(d: myOffset, to: r._time), _field: r._field, _value: r._value}))
|> yield()