0

I've filled database (Influxdb 2.0.7) so it contains value of 1 each day in year:

                    _time:time                  _value:int  
------------------------------  --------------------------  
2021-01-01T00:00:00.000000000Z                           1  
2021-01-02T00:00:00.000000000Z                           1  
2021-01-03T00:00:00.000000000Z                           1  
2021-01-04T00:00:00.000000000Z                           1  
2021-01-05T00:00:00.000000000Z                           1  
2021-01-06T00:00:00.000000000Z                           1  
...

Now I would like to query the database so it sums ones in each month:

from(bucket: "my_bucket")
|> range(start: 1609459200, stop: 1640995200)
...
|> window(every: 1mo, offset: 0s, createEmpty: true)
|> sum()

This works correct for 0s offset:

Table: keys: [_start, _stop]
                   _start:time                      _stop:time                  _value:int  
------------------------------  ------------------------------  --------------------------  
2021-01-01T00:00:00.000000000Z  2021-02-01T00:00:00.000000000Z                          31  
Table: keys: [_start, _stop]
                   _start:time                      _stop:time                  _value:int  
------------------------------  ------------------------------  --------------------------  
2021-02-01T00:00:00.000000000Z  2021-03-01T00:00:00.000000000Z                          28  
Table: keys: [_start, _stop]
                   _start:time                      _stop:time                  _value:int  
------------------------------  ------------------------------  --------------------------  
2021-03-01T00:00:00.000000000Z  2021-04-01T00:00:00.000000000Z                          31  
Table: keys: [_start, _stop]
                   _start:time                      _stop:time                  _value:int  
------------------------------  ------------------------------  --------------------------  
2021-04-01T00:00:00.000000000Z  2021-05-01T00:00:00.000000000Z                          30 
...

But if I set offset to negative number (-7200s = shift interval to +02:00 timezone offset) it returns strange results:

from(bucket: "my_bucket")
|> range(start: 1609459200, stop: 1640995200)
...
|> window(every: 1mo, offset: -7200s, createEmpty: true)
|> sum()

Note how 3rd month has 28 items instead of 31 and the window ends with 2021-03-28 instead of 2021-03-31T22:00:00.

Table: keys: [_start, _stop]
                   _start:time                      _stop:time                  _value:int  
------------------------------  ------------------------------  --------------------------  
2021-01-01T00:00:00.000000000Z  2021-01-31T22:00:00.000000000Z                          31  
Table: keys: [_start, _stop]
                   _start:time                      _stop:time                  _value:int  
------------------------------  ------------------------------  --------------------------  
2021-01-31T22:00:00.000000000Z  2021-02-28T22:00:00.000000000Z                          28  
Table: keys: [_start, _stop]
                   _start:time                      _stop:time                  _value:int  
------------------------------  ------------------------------  --------------------------  
2021-02-28T22:00:00.000000000Z  2021-03-28T22:00:00.000000000Z                          28  
Table: keys: [_start, _stop]
                   _start:time                      _stop:time                  _value:int  
------------------------------  ------------------------------  --------------------------  
2021-03-31T22:00:00.000000000Z  2021-04-30T22:00:00.000000000Z                          30

Any idea why Flux language returns such results? Or am I querying something wrong?

Michal Špondr
  • 1,337
  • 2
  • 21
  • 44

1 Answers1

0

ok i found solution:

offset is bugged, trunk march to 28 days when u apply offset

this is solution for me:

import "timezone"
option location = timezone.location(name: "Europe/Rome")
from(bucket: "solaredge_cloud")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["type"] == "production")
  |> filter(fn: (r) => r["_field"] == "wh")
  |> aggregateWindow(every: 1mo, fn: sum, createEmpty: false)
  |> timeShift(duration: -1s, columns: ["_time"])

chage the use of "offset" in "timeshift". timeshift isnt bugged and u got table with 31 days sum from march too

carlo
  • 1
  • Good to know offset is bugged. I'd expect such basic functionality is working in version 2. Maybe Influxdb is not as mature as I've expected. – Michal Špondr Apr 04 '22 at 11:45
  • I'm also struggling with windows and integrals wrt to timezone, especially for clustering energy time slots (F1, F2, F3). What i don't understand is whether option location statement is somewhat global and why timeShift in your case is shifting by -1s. We have the same Timezone (Rome) but if i explore my data UTC 22:00 of the day before shall be 00:00 of today in my timezone but the data is instead cut and starts directly from 02:00 (which is partially fine) – user217354 Sep 29 '22 at 07:23
  • If i use timeShift(durante: 2h, ...) the data is ok but that manual "2h" is a little bit tricky to have it not calculated from UTC and the chosen timezone – user217354 Sep 29 '22 at 07:29