I store the energy consumption of devices per hour at the end of each hour.
Now I want to display an aggregation of the consumption of the last 7 days. The time should be relative, so start: -7d.
In this case the first record is only pro rata and not the whole day:
But I want to start with a whole day.
I have managed a solution with “start: -8d & sort desc → limit n:7 → sort asc”:
> from(bucket: "iobroker")
> |> range(start: -8d)
> |> filter(fn: (r) => r["_measurement"] == "Strom.Heizung.Energie_in_der_letzten_Stunde")
> |> drop(columns: ["_field", "ack", "from"])
> |> aggregateWindow(every: 1d, fn: sum, createEmpty: false)
> |> timeShift(duration: -1s)
> |> sort(columns: ["_time"], desc: true)
> |> limit(n:7)
> |> sort(columns: ["_time"], desc: false)
This works, but it looks a bit messy and not very elegant. Is there a better way?
Thanks
Frank