SQL Find Holes in Linear Time-Based Table

Question

Hi, I have a linear datetime based table in MySQL and SQL server for a time-based data collection system. There is required to be a new entry in this table that will be timestamped exactly every 10 seconds. Sometimes, the data collection source goes down for a while and comes back up, at which point the newest data is inserted into the table, and a new thread is spawned to start back-filling in the "holes".

Is there a SQL statement I could use to find the last "hole" in the ten second values so that I would know where to start asking for backup data?

Thanks!

Answer 1

for SQL Server, try something like this:

SELECT TOP 1 
    DATEADD(second,-10,t1.YourDateColumn)
    FROM YourTable t1
    WHERE NOT EXISTS (SELECT 1
                          FROM YourTable t2 
                          WHERE DATEADD(second,-10,t1.YourDateColumn) = t2.YourDateColumn)
    ORDER BY t1.YourDateColumn DESC