[0.9.4] Query with time > now() - 7d only returns last three days

[larsakeekstrand]

Hi,

when I try to get the last 7 days of data, I only get the last three days. The same applies when asking for 4,5,6,7,8 days of data or when asking for the equivalent hours. However, when I ask for the last 9 days (or 206h) everything works fine.

Example when asking for last 7d:

SELECT value FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > now() - 7d

2015-09-21T00:12:09.625Z    24.6
2015-09-21T00:27:09.403Z    23.9
...
2015-09-23T13:14:13.222Z    20.6

Example when asking for last 9d:

SELECT value FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > now() - 9d

2015-09-14T17:54:01.158Z    20.5
2015-09-14T18:09:01.058Z    20.5
...
2015-09-23T13:14:13.222Z    20.6

[rkuchan]

@larsakeekstrand, to see if something is amiss could you try using COUNT to identify the number of points that you'd expect to get back from your query? Querying the past seven days seems to be working fine for me.

[larsakeekstrand]

When I run the query using COUNT, I get the same number when trying 3, 4, 5, 6, 7, 8d:

SELECT COUNT(value) FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > now() - 2d

193

SELECT COUNT(value) FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > now() - 3d

283

SELECT COUNT(value) FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > now() - 4d

283

SELECT COUNT(value) FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > now() - 8d

283

SELECT COUNT(value) FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > now() - 9d

1020

They same behavior occurs also at a higher day-count i.e. 10,11,12,13,14,15 all give the same count.

Trying this gives nothing back even if there should be samples:

SELECT COUNT(value) FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > now() - 4d AND time < now() - 3d

There are around 50-150 values / day in the dataset. To many to list here, so I have extracted some samples:

SELECT value FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > now() - 9d

2015-09-14T22:24:00.747Z    20.5
2015-09-14T22:39:01.415Z    20.6
...
2015-09-15T00:09:01.006Z    20.6
2015-09-15T00:24:00.833Z    20.6
...
2015-09-16T00:03:00.557Z    21.8
2015-09-16T00:03:01.346Z    21.8
...
2015-09-17T00:01:00.877Z    20.4
2015-09-17T00:01:03.182Z    20.4
...
2015-09-18T00:05:04.155Z    21
2015-09-18T00:20:04.809Z    21
...
2015-09-19T00:05:06.284Z    21.4
2015-09-19T00:20:06.101Z    21.4
...
2015-09-20T00:05:07.871Z    20.4
2015-09-20T00:20:07.735Z    20.4
...
2015-09-21T00:12:09.625Z    24.6
2015-09-21T00:27:09.403Z    23.9
...
2015-09-22T00:12:11.246Z    32.4
2015-09-22T00:27:11.01Z 29.5
...
2015-09-23T00:14:12.653Z    21
2015-09-23T00:29:12.447Z    21

[larsakeekstrand]

Now, when I try again 9h later when I have passed into a new day and try again, the threshold (time) haven't moved. Now, I get the same answer for 4, 5, 6, 7, 8, 9d instead which seems to indicate that this doesn't relate to the query but the measurements somehow.

There seems to be a time-window that I cannot query correct:

SELECT value FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > '2015-09-20T00:00:00.0Z'

2015-09-21T00:12:09.625Z    24.6       <---- Should be 2015-09-20...
2015-09-21T00:27:09.403Z    23.9
...

SELECT value FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > '2015-09-18T00:00:00.0Z'

2015-09-21T00:12:09.625Z    24.6     <---- Should be 2015-09-18...
2015-09-21T00:27:09.403Z    23.9
...

SELECT value FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > '2015-09-15T00:00:00.0Z'

2015-09-21T00:12:09.625Z    24.6     <---- Should be 2015-09-15...
2015-09-21T00:27:09.403Z    23.9
...

SELECT value FROM "metric.0002" WHERE gw='A8404113CE8B' AND time > '2015-09-14T00:00:00.0Z'

2015-09-14T22:24:00.747Z    20.5     <---- Now, things works again and we also get the missing measurements below.
2015-09-14T22:39:01.415Z    20.6
...
2015-09-15T00:09:01.006Z    20.6
2015-09-15T00:24:00.833Z    20.6
...
2015-09-16T00:03:00.557Z    21.8
2015-09-16T00:03:01.346Z    21.8
...
2015-09-17T00:01:00.877Z    20.4
2015-09-17T00:01:03.182Z    20.4
...
2015-09-18T00:05:04.155Z    21
2015-09-18T00:20:04.809Z    21
...
2015-09-19T00:05:06.284Z    21.4
2015-09-19T00:20:06.101Z    21.4
...
2015-09-20T00:05:07.871Z    20.4
2015-09-20T00:20:07.735Z    20.4
...
2015-09-21T00:12:09.625Z    24.6
2015-09-21T00:27:09.403Z    23.9
...
2015-09-22T00:12:11.246Z    32.4
2015-09-22T00:27:11.01Z 29.5
...
2015-09-23T00:14:12.653Z    21
2015-09-23T00:29:12.447Z    21

If I try to query beyond this i.e. "2015-09-13T00:00:00.0Z", things get "stuck" again which indicates that there is more similar "bad time-windows" in the measurements.

[larsakeekstrand]

Updated to 0.9.4.1 but the problem is still there.

/Regards
Lars-Ake

[beckettsean]

@larsakeekstrand we can't repro and I've seen no other reports that look like this (and it seems likely others would notice).

Can we get a copy of your data? Just make a gzipped tarball of everything in the influxdb directory (e.g. /wal, /data, /meta, /hh). You can upload it to http://cwok.me/cu/gh4209 or we can arrange other means if you prefer.

[larsakeekstrand]

@beckettsean Sorry for a late response, but I have uploaded a tarball of my db to http://cwok.me/cu/gh4209 now. Use the queries above to reproduce the issue.

[beckettsean]

thanks, @larsakeekstrand I see the data.

[jsternberg]

The query engine was replaced in 0.11 so this issue likely isn't valid anymore. If this issue still exists with the most recent version of InfluxDB, please reopen this issue. Thanks!