influxdata / influxdata/influxdb
Inconsistent output for weekly groupings with offset and timezone parameter during time change
- Dominant language
- Rust
- Stars
- 31.7k
- Forks
- 3.7k
- Avg merge
- 13h 37m
- Merged PRs (30d)
- 8
Description
__Steps to reproduce:__
1. Connect to Influx using `-precision rfc339` option, like `influx --database --username --password -precision rfc3339`
2. Write a query that include a change hour from winter time to summer time grouping by week and starting from monday (4 days offset), for example with italian timezone:
```
SELECT sum("value") FROM "datapoint_values" WHERE "plant"='0001' AND "datapoint"='PWER' AND time >= '2020-03-20T00:00:00Z' AND time <= '2020-04-30T00:00:00Z' GROUP BY time(1w,4d) fill(0) tz('Europe/Rome')
```
__Expected behavior:__
```
time sum
---- ---
2020-03-16T00:00:00+01:00 882795
2020-03-23T00:00:00+01:00 818340
2020-03-30T00:00:00+02:00 713050
2020-04-06T00:00:00+02:00 477533
2020-04-13T00:00:00+02:00 418878
2020-04-20T00:00:00+02:00 424883
2020-04-27T00:00:00+02:00 187801
```
__Actual behavior:__
```
time sum
---- ---
2020-03-16T00:00:00+01:00 882795
2020-03-23T00:00:00+01:00 818340
2020-03-30T00:00:00+02:00 0
2020-03-30T01:00:00+02:00 713050
2020-04-06T00:00:00+02:00 477533
2020-04-13T00:00:00+02:00 418878
2020-04-20T00:00:00+02:00 424883
2020-04-27T00:00:00+02:00 187801
```
An additional line with zero result is printed (`2020-03-30T00:00:00+02:00 0`), also one line time is not midnight located (`2020-03-30T01:00:00+02:00 713050`)
__Additional info:__
* The same behavior occurs with an offset of days whose module is> = 4 and <= 6
* With offset day time null or < 4 (unitl sunday) the output looks like consistent:
```
SELECT sum("value") FROM "datapoint_values" WHERE "plant"='0001' AND "datapoint"='PWER' AND time >= '2020-03-16T00:00:00Z' AND time <= '2020-04-30T00:00:00Z' GROUP BY time(1w,3d) fill(0) tz('Europe/Rome')
name: datapoint_values
time sum
---- ---
2020-03-15T00:00:00+01:00 846944
2020-03-22T00:00:00+01:00 1100599
2020-03-29T00:00:00+01:00 712901
2020-04-05T00:00:00+02:00 496406
2020-04-12T00:00:00+02:00 417564
2020-04-19T00:00:00+02:00 424568
2020-04-26T00:00:00+02:00 247997
SELECT sum("value") FROM "datapoint_values" WHERE "plant"='0001' AND "datapoint"='PWER' AND time >= '2020-03-16T00:00:00Z' AND time <= '2020-04-30T00:00:00Z' GROUP BY time(1w) fill(0) tz('Europe/Rome')
name: datapoint_values
time sum
---- ---
2020-03-12T00:00:00+01:00 243645
2020-03-19T00:00:00+01:00 1472722
2020-03-26T00:00:00+01:00 657964
2020-04-02T00:00:00+02:00 596316
2020-04-09T00:00:00+02:00 425270
2020-04-16T00:00:00+02:00 418544
2020-04-23T00:00:00+02:00 427514
2020-04-30T00:00:00+02:00 5004
```
__Environment info:__
* Linux 4.14.193-113.317.amzn1.x86_64 x86_64
* InfluxDB v1.8.2 (git: 1.8 6a1299e8c6c81ef8fd573ed2b700217121baf04e)
* InfluxDB shell version: 1.8.2
same behavior with updated version InfluxDB v1.8.3 (git: 1.8 563e6c3d1a7a2790763c6289501095dbec19244e)
Contributor guide
Research direction
Start by reproducing the SQL query from the issue with InfluxDB 1.8.2 or 1.8.3, using the Europe/Rome timezone and a one-week grouping with a four-day offset. Trace the weekly grouping and timezone handling around the March 2020 daylight-saving change; done means the March 30 bucket remains at midnight without an extra zero-result row, while the reported sums remain unchanged.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- rust
- Domain
- database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100