Hi, I managed to get exactly what you need using flux:
import "experimental"
import "date"
baseTime = v.timeRangeStart
timeStart = date.truncate(t: experimental.subDuration(d: 7d, from: now()), unit: 1d)
timeStop = date.truncate(t: experimental.addDuration(d: 1d, to: now()), unit: 1d)
energyUsage = from(bucket:"smartmeter/autogen")
|> range(start: timeStart, stop: timeStop)
|> filter(fn: (r) => r._field == "EnergyImportTotal")
|> aggregateWindow( every: 1h, fn: mean)
|> window(every: 1d)
|> stateDuration(
fn: (r) => true,
column: "timeDiff",
unit: 1ns
)
|> map(fn: (r) =>
({ r with _time: time(v: (int(v: baseTime) + r.timeDiff)) })
)
|> drop(columns: ["timeDiff"])
|> group(columns: ["_time", "Serial"])
|> mean()
|> group(columns: ["Serial"])
|> yield()
What this does is basically calculating the hourly averages, then moves the times of all measurements to the same day and calculates the averages again. Depending on your data structure the query has to be adapted.
