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
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?