Hi all,
I have data stored from my energy meter which looks like this:
after this query:
currentvalues = from(bucket: "smarthome")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "ElectricalEnergy")
|> filter(fn: (r) => r["Scope"] == "House")
|> filter(fn: (r) => r["Source"] == "SaiaBurgess")
|> filter(fn: (r) => r["UpdateInterval"] == "Day")
|> filter(fn: (r) => r["_field"] == "value")
|> truncateTimeColumn(unit: 1m)
So, a value every day at midnight (in reality, they are some seconds after midnight why I truncate them).
How can I now get the monthly usage? I’ve tried so many things with aggregateWindow, timeshift and all that it would take too long to explain it all here. I think one of the “problem” is, is that the consumption of e.g. January hast to be calculated until 00:00 on Feb 1st. As this is already Feb. I don’t know the correct way of taking this into account.
Did anyone already do that?
Many thanks,
Hello @Sparx82 ,
opened 09:21PM - 13 Jul 22 UTC
enhancement
`window()` and `aggregateWindow()` window based on Unix epoch time `1970-01-01`.… It's necessary to use an offset, which is available in both `window()` and `aggregateWindow()` functions, in order to window based on the range start date.
For example, for a 30d window based on the start date, without an offset, the first window is only 7 days long:
```js
import "experimental/array"
import "date"
start_date = time(v: 2021-11-22T00:00:00.000Z)
data_raw = [
{_time: 2021-11-22T00:10:00.000Z, id: "bar", _value: 5},
{_time: 2021-11-26T00:00:00.000Z, id: "bar", _value: 10},
{_time: 2021-12-03T00:00:00.000Z, id: "bar", _value: 30},
{_time: 2022-01-05T00:00:00.000Z, id: "bar", _value: 50}
]
data = array.from(rows: data_raw)
|> group(columns: ["id"])
data
|> range(start: start_date, stop: date.add(d: 60d, to: time(v: start_date)))
|> window(every: 30d)
```
<img width="1525" alt="Screen Shot 2022-07-13 at 2 15 10 PM" src="https://user-images.githubusercontent.com/330044/178837649-51d9805f-afb4-474b-b3b9-9d06a1f7b210.png">
To window by 30 days from the start, it's necessary to add an `offset` like:
```js
import "math"
...
days_since_epoch = uint(v: start_date) / uint(v: duration(v: 1d))
offset = duration(v: string(v: math.mod(x: float(v: days_since_epoch), y: float(v: days_since_epoch / uint(v: 30) * uint(v: 30)))) + "d")
...
|> window(every: 30d, offset: offset)
```
<img width="1525" alt="Screen Shot 2022-07-13 at 2 17 48 PM" src="https://user-images.githubusercontent.com/330044/178838010-69aa5f09-9a52-49f8-bd6b-f6c7d9768713.png">
It would be easier if there were an option in `window()` and `aggregateWindow()` to start the windowing from the start date rather than epoch time.
Including Feb1 00:00 because this is the value of the last day of January.
Hello @Sparx82 ,
