SQL: Comparing a whole row of data to a number

Question

I'm basically logging data to an SQL database every minute, but I want to be able to check to see if there are any entries missing (ie: see if there are any differences greater than a minute).

Take this for example:

5 4 2 1

I would want to display the rows where the 4 and 2 exist so that I know there is something missing between. The actual data is in the following format.

date_time

2010-09-12 10:45:00

2010-09-12 10:44:00

etc..

I have discovered how to extract the minute value using EXTRACT(MINUTE FROM date_time) however I'm quite lost as to how to do a comparison over the whole table.

Any help would be awesome.

Thanks

Answer 1

MySQL doesn't have recursive functionality, so you're left with using the NUMBERS table trick -

1. Create a table that only holds incrementing numbers - easy to do using an auto_increment:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. Populate the table using:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...for as many values as you need.

3. Use DATE_ADD to construct a list of dates, increasing the days based on the NUMBERS.id value. Replace "2010-06-06" and "2010-06-14" with your respective start and end dates (but use the same format, "YYYY-MM-DD HH:MM:SS") -

   SELECT x.*
     FROM (SELECT DATE_ADD('2010-06-06', INTERVAL n.id - 1 MINUTE)
             FROM numbers n
            WHERE DATE_ADD('2010-06-06', INTERVAL n.id -1 MINUTE) <= '2010-06-14' ) x
   

4. LEFT JOIN onto your table of data based on the time portion:

      SELECT x.ts AS timestamp,
             COALESCE(yt.col, 'missing') AS cnt
        FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 MINUTE), '%Y-%m-%d %k:%i') AS ts
                FROM numbers n
               WHERE DATE_ADD('2010-06-06', INTERVAL n.id - 1 MINUTE) <= '2010-06-14') x
   LEFT JOIN YOUR_TABLE yt ON DATE_FORMAT(yt.date, '%Y-%m-%d %k:%i') = x.ts
   

See the documentation for more info on the DATE_FORMAT function.