Hey there!
from(bucket: "climate")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "temperature_sensor")
|> filter(fn: (r) => r["_field"] == "value")
|> aggregateWindow(every: 1d, fn: max, createEmpty: false)
|> yield(name: "max")
Setting the custom time-range in the Data Explorer to2023-04-09 00:00:00 to 2023-04-09 23:59:59
I would expect only ONE. So why am I getting two? … or: why do I get the first line with _time = 2023-04-09 02:00:00 GMT+2 ?
Messing around I tried to change the time range, the aggregate-period (1d, 24 h, 23 h, 25 h,…)… but the result stays (more or less) the same.
What am I doing wrong? How can I change my query to get only one (the right) result?
Thanks a lot!
Hello @Hesindian ,
opened 09:21PM - 13 Jul 22 UTC
closed 01:47AM - 09 Apr 23 UTC
enhancement
no-issue-activity
`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.
I hope it makes sense what’s happening now!
However if you just want the min, max, etc I recommend using those functions instead of aggregateWindow
data = from(bucket: "climate")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "temperature_sensor")
|> filter(fn: (r) => r["_field"] == "value")
data |> max()
|> yield(name: "max")
data |> min()
|> yield(name: "min")
Hello @Anaisdg ,aggregateWindow() function | Flux 0.x Documentation ).
Best
