influxdata / influxdata/influxdb

Inconsistent output for weekly groupings with offset and timezone parameter during time change

Open
#19,676 2 comments 4 reactions 0 assignees View on GitHub
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.