Hi All,
I have the following data:
Now I want to shift that one month backwards, meaning that it starts January 1st, and then February 1st, Match 1st and so on. But if I now use:
|> timeShift(duration: -1mo)
I get the following result:
which looks like timeshift just substracts a fixed amount of days instead of a correct calendar month.
Best regards,
Hello @Sparx82 ,
Sparx82
November 2, 2022, 10:52am
3
I’m using InfluxDB v2.4.0.
I investigated a little bit further and it looks like it is a problem with the timezone.
Without any timezone settings and without timeshift:
Without any timezone settings and with timeshift:
Now I add my timezone setting:
option location = timezone.location(name: "Europe/Zurich")
Please note that I have an aggreagteWindow in there, which supports the timezone as far as I know. But still strange.
|> truncateTimeColumn(unit: 1m)
|> aggregateWindow(every: 1mo, fn: first, createEmpty: true)
You can download the CSV data here:
It came from the following command:
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)
|> aggregateWindow(every: 1mo, fn: first, createEmpty: true)
|> yield()
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.
