# \[Solved\]How to find gaps in my data?

**URL:** <https://community.influxdata.com/t/solved-how-to-find-gaps-in-my-data/2982>\
**Category:** Systems\
**Tags:** influxdb, time\
**Created:** [November 6, 2017, 10:44am UTC](https://community.influxdata.com/t/solved-how-to-find-gaps-in-my-data/2982 "2017-11-06T10:44:25Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Moran](https://avatars.discourse-cdn.com/v4/letter/m/cc9497/32.png) [@Moran](https://community.influxdata.com/u/Moran)\
**Post date:** [November 6, 2017, 10:44am UTC](https://community.influxdata.com/t/solved-how-to-find-gaps-in-my-data/2982/1 "2017-11-06T10:44:25Z")

</div>

I’m looking for a query that will return to me all of the times where i’m missing data for over than 30 minutes (because of some bug or disconnection).

Lets say I have this data:

## time [other data (irrelevant)…]

2017-11-01T20:00:00.000000000Z  
2017-11-01T20:01:00.000000000Z  
2017-11-01T20:02:00.000000000Z  
2017-11-01T20:03:00.000000000Z  
2017-11-01T20:50:00.000000000Z  
2017-11-01T20:51:00.000000000Z

I would like the query to return:

time

* * *

2017-11-01T20:03:00.000000000Z

How can I write that query?  
Thanks in advance.

---

<div class="post-metadata">

**Author:** ![Moran](https://avatars.discourse-cdn.com/v4/letter/m/cc9497/32.png) [@Moran](https://community.influxdata.com/u/Moran)\
**Post date:** [November 6, 2017, 2:42pm UTC](https://community.influxdata.com/t/solved-how-to-find-gaps-in-my-data/2982/2 "2017-11-06T14:42:36Z")

</div>

I found the answer I was looking for:

select \* from (SELECT max(“money”) FROM table WHERE time \> ‘2017-07-30T21:00:00Z’ GROUP BY time(2m) fill(-10000)) where max = -10000;

Couldn’t query with nulls so I just inserted some values of -10000 to the nules, there might be a better solution but this one works for me.

---

<div class="post-metadata">

**Author:** ![dandv](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/dandv/32/5524_2.png) [@dandv](https://community.influxdata.com/u/dandv)\
**Post date:** [March 31, 2020, 8:28pm UTC](https://community.influxdata.com/t/solved-how-to-find-gaps-in-my-data/2982/3 "2020-03-31T20:28:02Z")

</div>

Interesting hack, but it has a few problems:

1. It doesn’t show the exact time where a gap starts, but a time multiple of the GROUP BY value (`2m`)
2. It shows every single gap separately, even if they form a continuous gap. A 10-minute gap will show as 5 `2m` gaps.

Here’s a better and simpler solution, using a subquery:

```auto
SELECT * FROM (
  SELECT ELAPSED("money", 1m) AS "gap"
  FROM table
  WHERE ...
) WHERE gap >= 2

```

This addressed both problems above; it will show

- the exact points at the ends of the gaps, and
- the exact contiguous length of the gaps bigger than 2 minutes, no matter how long they are.

Would be nice if you could run [`SELECT ELAPSED(...) AS "gapSize" WHERE gapSize > ...`](https://github.com/influxdata/influxdb/issues/5898) directly. Worth an upvote?

---

<div class="post-metadata">

**Author:** ![blharvey](https://avatars.discourse-cdn.com/v4/letter/b/eada6e/32.png) [@blharvey](https://community.influxdata.com/u/blharvey)\
**Post date:** [June 3, 2022, 6:47pm UTC](https://community.influxdata.com/t/solved-how-to-find-gaps-in-my-data/2982/4 "2022-06-03T18:47:59Z")

</div>

What if the gap is at the start of your data? Won’t the elapsed function miss those records. For instance, if I set my query for -7d but the last available record is -5d, since the elapsed function takes differences between records, wont this miss it.

If it does, is there a way to include the missing records in the above query?