Find "missing" rows

[ccranton]

I have several tables that have a row inserted once a minute by a remote application.

How can I query for missed inserts?

i.e. For the extract below, the query would return.

16/08/2007 16:31:35
16/08/2007 16:36:35


Table Extract

16/08/2007 16:30:35
16/08/2007 16:32:35
16/08/2007 16:33:35
16/08/2007 16:34:35
16/08/2007 16:35:35
16/08/2007 16:37:35

 

[JarlH]

Alternative 1:
Create a help table with all expected time stamps. Then you do something like:
[tt]SELECT ts FROM helptable
WHERE ts NOT IN (SELECT ts FROM maintable)[/tt]

Alternative 2:
A stored procedure that fetches one row and verifies the previous row is one minute older.

Alternative 3:
[tt]SELECT ts+1 FROM maintable
WHERE ts+1 NOT IN (SELECT ts FROM maintable)[/tt]

Note: will only return the first missing row even if it's a several minute gap.