CODEWITHSUNDEEP
sqlsqlitegaps-and-islands
a_little_help_please83891
a_little_help_please83891

Reputation: 56

How do I find gap in sqlite table?

I have a sqlite table with timestamps in milliseconds as primary key each row should be 1 second or 1000 apart from one another. Sometimes my data recorder goes out and there is no data in the table for that time. How can I find the gaps using a SQL statement? A cursor based solution is possible I know.

table = PVT
TS
1119636081000
1119636082000
1119636083000
1119636084000
1119636085000
------gap------
1119636090000
1119636091000

Upvotes: 2

Views: 1067

Answers (4)

feihtthief
feihtthief

Reputation: 7083

Create a tally table with at least 86400 rows (one per second for a day):

create table Tally(n integer primary key not null);
insert into Tally(n) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
insert into Tally(n) select null from tally n1 , tally n2, tally n3, tally n4, tally n5;

Join your PVT table onto the transposed records for the day:

select 1119636081000 + tally.n*1000 as Expected, pvt.ts from tally left join pvt on pvt.ts = 1119636081000 + tally.n*1000 where tally.n <= 86400 limit 15;

Given a table I populated with your sample data I get this as an output:

Expected       TS
-------------  -------------
1119636081000  1119636081000
1119636082000  1119636082000
1119636083000  1119636083000
1119636084000  1119636084000
1119636085000  1119636085000
1119636086000
1119636087000
1119636088000
1119636089000
1119636090000  1119636090000
1119636091000  1119636091000
1119636092000
1119636093000
1119636094000
1119636095000

If you then additionally filter where PVT.TS is null, you should get the missing values:

select 1119636081000 + tally.n*1000 as Expected, pvt.ts from tally left join pvt on pvt.ts = 1119636081000 + tally.n*1000 where tally.n <= 86400 and PVT.ts is null limit 15;

Expected       TS
-------------  ----------
1119636086000
1119636087000
1119636088000
1119636089000
1119636092000
1119636093000
1119636094000
1119636095000
1119636096000
1119636097000
1119636098000
1119636099000
1119636100000
1119636101000
1119636102000

NOTE: I'm using limit 15 to keep myself sane at the console

Upvotes: 0

ramana_k
ramana_k

Reputation: 1933

This may work. Assuming the table name is "tstamps",

select a.ts
from tstamps a
where not exists
   (select b.ts
    from tstamps b
    where b.ts = a.ts+1000)
and exists
   (select c.ts
    from tstamps c
    where c.ts = a.ts+2000)

Another way

select a.ts
from tstamps a
where not exists
   (select b.ts
    from tstamps b
    where b.ts = a.ts+1000)
and a.ts <
   (select max(c.ts)
    from tstamps c
   )

Using MINUS operator. I am not sure, which of these queries does better performance wise.

select ts+1000
from pvt
where ts != (select max(ts) from pvt)
minus
select ts
from pvt
where ts != (select min(ts) from pvt)

Upvotes: 2

pilcrow
pilcrow

Reputation: 58681

At the time of this writing SQLite does not support window functions like LAG(TS) OVER (ORDER BY TS ASC) nor LEAD() OVER, which would easily give you the previous and following TS values, respectively.

So, you need to do it yourself:

sqlite> .mode col
sqlite> .width 14 14 14
sqlite>    SELECT PVT.TS  AS measurement,
                  prev.TS AS prev,
                  next.TS AS next
             FROM PVT
        LEFT JOIN PVT next ON PVT.TS = (next.TS - 1000)
        LEFT JOIN PVT prev ON PVT.TS = (prev.TS + 1000);

This will give you something like this (I used different data, as you'll see):

-- measurement        prev            next     
  -------------   -------------   -------------
  1119636081000                   1119636082000   -- gap (no previous at all)
  1119636082000   1119636081000   1119636083000 
  1119636083000   1119636082000   1119636084000 
  1119636084000   1119636083000   1119636085000 
  1119636085000   1119636084000                   -- gap (no next offset 1000)
  1119636088000                   1119636089000   -- gap (no previous offset 1000)
  1119636089000   1119636088000                   -- gap (no next at all)

You can always restrict that query to only those records WHERE prev.TS IS NULL OR next.TS is NULL, too.

Upvotes: 0

Skavenger0
Skavenger0

Reputation: 63

Something like this (Assuming PVT.TS is your Column name):

SELECT * FROM 'table' WHERE PVT.TS ISNULL;

or

SELECT * FROM 'table' WHERE PVT.TS IS NULL;

If your collector is actually entering a blank entry you might need 

WHERE PVT.TS = ''

or

where ifnull(some_column, '') = ''

Upvotes: 0

Related Questions