[Solved]How to find gaps in my data?

,
1

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.

2

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.

1 Like
3

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:

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 > ... directly. Worth an upvote?

2 Likes
4

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?