influxdata / influxdata/influxdb

positive and negative time interval offsets behave differently around DST boundaries

Open
#9,130 11 comments 2 reactions 0 assignees View on GitHub
1.x area/queries kind/bug
Dominant language
Rust
Stars
31.7k
Forks
3.7k
Avg merge
13h 37m
Merged PRs (30d)
8

Description

### Bug report

InfluxDB version: 1.3.5
OS: centos-release-7-3.1611.el7.centos.x86_64
Docker container running on macos 10.12.6

#### Steps to reproduce:

1. Create a bunch of data before/after the beginning and end of DST.

The following sample inserts one record per day at 12pm for the America/New_York time zone for 14 consecutive days starting March 7, 2017
```
insert dst_test value=1 1488906000000000000
insert dst_test value=1 1488992400000000000
insert dst_test value=1 1489078800000000000
insert dst_test value=1 1489165200000000000
insert dst_test value=1 1489251600000000000
insert dst_test value=1 1489334400000000000
insert dst_test value=1 1489420800000000000
insert dst_test value=1 1489507200000000000
insert dst_test value=1 1489593600000000000
insert dst_test value=1 1489680000000000000
insert dst_test value=1 1489766400000000000
insert dst_test value=1 1489852800000000000
insert dst_test value=1 1489939200000000000
insert dst_test value=1 1490025600000000000
```

The following sample inserts one record per day at 12pm for the America/New_York time zone for 14 consecutive days starting November 1, 2017.
```
insert dst_test value=1 1509552000000000000
insert dst_test value=1 1509638400000000000
insert dst_test value=1 1509724800000000000
insert dst_test value=1 1509811200000000000
insert dst_test value=1 1509901200000000000
insert dst_test value=1 1509987600000000000
insert dst_test value=1 1510074000000000000
insert dst_test value=1 1510160400000000000
insert dst_test value=1 1510246800000000000
insert dst_test value=1 1510333200000000000
insert dst_test value=1 1510419600000000000
insert dst_test value=1 1510506000000000000
insert dst_test value=1 1510592400000000000
insert dst_test value=1 1510678800000000000
```

2. Attempt to retrieve data grouped by 7d intervals beginning midnight of 3/7 and 11/1, using positive and negative offsets

Query 1: around start of DST, using negative offset

select count(value) from dst_test where time > '2017-03-07 00:00:00' and time < '2017-03-21 00:00:00' group by time(7d, -2d) tz('America/New_York')

Query 2: around start of DST, using positive offset

select count(value) from dst_test where time > '2017-03-07 00:00:00' and time < '2017-03-21 00:00:00' group by time(7d, 5d) tz('America/New_York')

Query 3: around end of DST, using negative offset

select count(value) from dst_test where time > '2017-11-01 00:00:00' and time < '2017-11-15 00:00:00' group by time(7d, -1d) tz('America/New_York')

Query 4: around end of DST, using positive offset

select count(value) from dst_test where time > '2017-11-01 00:00:00' and time < '2017-11-15 00:00:00' group by time(7d, 6d) tz('America/New_York')

#### Expected behavior

First two queries should both include
```
time count
---- -----
2017-03-07T00:00:00-05:00 7
2017-03-14T00:00:00-04:00 7
```

Second two queries should both include
```
time count
---- -----
2017-11-01T00:00:00-04:00 7
2017-11-08T00:00:00-05:00 7
```

#### Actual behavior

Query 1
```
time count
---- -----
2017-02-28T00:00:00-05:00 0
2017-03-07T00:00:00-05:00 7
2017-03-14T00:00:00-04:00 7
```

Query 2 (this is the strangest result to me)
```
time count
---- -----
2017-02-28T00:00:00-05:00 0
2017-03-07T00:00:00-05:00 7
2017-03-14T00:00:00-04:00 0
2017-03-14T01:00:00-04:00 7
```

Query 3
```
time count
---- -----
2017-10-25T00:00:00-04:00 0
2017-11-01T00:00:00-04:00 7
2017-11-08T00:00:00-05:00 7
```

Query 4
```
time count
---- -----
2017-10-25T00:00:00-04:00 0
2017-11-01T00:00:00-04:00 14
```

Contributor guide

Open the contributing guide

Research direction

Reproduce the four InfluxQL queries with the supplied DST-spanning data in America/New_York and compare positive and negative offsets. Trace the time-grouping and timezone handling used by the query engine; done means both offset forms produce the expected two seven-day buckets without shifted or duplicate windows.

Written by the indexing model from the issue text.

Assessment

Tech stack
go
Domain
databases
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.