influxdata / influxdata/influxdb
positive and negative time interval offsets behave differently around DST boundaries
- 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
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