How to find time intervals with no data points in InfluxDB

0
votes

I have a bunch of IoT sensors that upload second by second data to InfluxDB. Since their network is unreliable, sometimes they do not report data.

I'm trying to figure out how to determine time periods in InfluxDB for which there is no data, and am encountering some wacky behavior with subqueries.

What I've tried so far:

Count the number of points each second, for example:

select count(power) 
from energy 
where time < '2017-05-14T00:05:10Z' 
  and time >= '2017-05-14T00:04:30Z' 
group by time(1s);

This looks promising, as it returns a result for each second in the interval and the count of data points:

...
1494720297000000000 1  
1494720298000000000 1
1494720299000000000 0
1494720300000000000 0
...

Now I want only the time periods where there are 0 points, however when I try this, only time ranges with non-zero numbers of points are reported:

select "points" 
from 
    (select count(power) as "points" 
     from energy 
     where time < '2017-05-14T00:05:10Z' 
       and time >= '2017-05-14T00:04:30Z' 
     group by time(1s));

Returns:

...
1494720297000000000 1
1494720298000000000 1

No data after 1494720298000000000 is returned, even though the subquery does return rows.

Any help would be appreciated in crafting a query or approach to identify only the areas of time where there is no data.

1
influxdb

1 Answers

1
votes

add fill(none) after your query

Example-select count(power)from energy where time < '2017-05-14T00:05:10Z' and time >= '2017-05-14T00:04:30Z' group by time(1s) fill(none)