Querying influxDB (one or multiple day data) seems fine in most cases, except when including a specific date (5th of January) as part of the time range.
Example queries that work fine (they all return data):
select * from "measurements" where time >= '2020-01-07T23:00:00.000Z' and time < '2020-01-08T23:00:00.000Z'
select * from "measurements" where time >= '2020-01-08T23:00:00.000Z' and time < '2020-01-09T23:00:00.000Z'
select * from "measurements" where time >= '2020-01-07T23:00:00.000Z' and time < '2020-01-12T23:00:00.000Z'
Example queries that don’t work: Result set is empty, and logs show status code 200, although I am sure there is data (based on the queries above):
select * from "measurements" where time >= '2020-01-05T23:00:00.000Z' and time < '2020-01-06T23:00:00.000Z'
select * from "measurements" where time >= '2020-01-05T23:00:00.000Z' and time < '2020-01-07T23:00:00.000Z'
select * from "measurements" where time >= '2020-01-05T23:00:00.000Z' and time < '2020-01-12T23:00:00.000Z'
Basically, when I include 2020-01-05T23:00:00.000Z as my beginning timestamp, all result sets are empty.
Could it be that my DB is corrupted? What can I do to fix this?
Thanks in advance!
Did you try to query starting from 5th Jan or before that up to Now ?
I am curious about the problem, we can debug it together
Thanks @Elbehery for offering your help!
I’ll summarise:
- Any query from back in time up until 5thJan2020 (or earlier) works fine ✓
- Any query from back in time until 6thJan2020, does not work ✘
- Querying from 5thJan2020 until 6thJan2020 does not work ✘
- Querying from 5thJan2020 until now (or any date after 6thJan2020) does not work ✘
- Querying from back in time until now (or any date after 6thJan2020) does not work ✘
- Any query from 6thJan2020 (or later) until now works fine ✓
So seems like the problem must reside between 5thJan2020 and 6thJan2020.
from your analysis, seems there is a problem in the data files from day 5th.
Can you provide a explain query plan ?
@Elbehery: Would this work?
> explain select * from "measurements" where time >= '2020-01-05T23:00:00.000Z' and time < '2020-01-06T23:00:00.000Z' and type = 'power' and myId = 'xxxxxx' fill(0) tz('Europe/Amsterdam')
QUERY PLAN
----------
EXPRESSION: <nil>
AUXILIARY FIELDS: myId::string, myId::tag, type::string, type::tag, value::float
NUMBER OF SHARDS: 2
NUMBER OF SERIES: 74710
CACHED VALUES: 0
NUMBER OF FILES: 0
NUMBER OF BLOCKS: 0
SIZE OF BLOCKS: 0
As you can see, the zeros everywhere 
Somehow the query evaluation returned nil, as you can see.
So the resultset should be empty.
Can you post the query plan from the queries that works fine ?
@Elbehery: I’m very sorry I made a mistake, please forget my previous comment.
This is the working query:
> explain select mean(value) as value from "measurements" where time >= '2020-01-06T23:00:00.000Z' and time < '2020-01-08T23:00:00.000Z' and type = 'power' and sensorId = 'xxxxx' group by time(1h) fill(0) tz('Europe/Amsterdam')
QUERY PLAN
----------
EXPRESSION: mean(value::float)
NUMBER OF SHARDS: 1
NUMBER OF SERIES: 1
CACHED VALUES: 0
NUMBER OF FILES: 1
NUMBER OF BLOCKS: 2
SIZE OF BLOCKS: 20259
This is the non working query (returning an empty result):
explain select mean(value) as value from "measurements" where time >= '2020-01-05T23:00:00.000Z' and time < '2020-01-08T23:00:00.000Z' and type = 'power' and sensorId = 'xxxxx' group by time(1h) fill(0) tz('Europe/Amsterdam')
QUERY PLAN
----------
EXPRESSION: mean(value::float)
NUMBER OF SHARDS: 2
NUMBER OF SERIES: 74710
CACHED VALUES: 0
NUMBER OF FILES: 12006
NUMBER OF BLOCKS: 24012
SIZE OF BLOCKS: 235695358
@Elbehery: Would it be possible that when there is a lot of data to be returned the result is empty, maybe because the machine where influx is running doesn’t have enough resources or some other reason?
However, if that was the case, I guess influx wouldn’t log status 200 for that request.
