Find gaps in mysql datetime sequence

Question

I have myTable with timestamp column the_dates (which is a unique index), and each value SHOULD be separated by five minutes.

For example:

the_dates
2014-03-13 17:30:00
2014-03-13 17:35:00
2014-03-13 17:40:00
2014-03-13 17:45:00

Is there a way, using mysql, to detect a "missing" row? For example if the next value was:

2014-03-13 17:55:00

then myTable would be missing:

2014-03-13 17:50:00

__

I was thinking of using UNIX_TIMESTAMP and detecting gaps larger than 300 seconds, perhaps using some sort of INNER JOIN to join the table to itself, but I'm too much of a novice to pull this off alone.

Any ideas?

Answer 1

You can try this. Each record returned should a time range that exceeds 5 minutes. For your example, it should have returned 2014-03-13 17:45:00 , 2014-03-13 17:55:00

SELECT t1.the_dates AS `from`, t2.the_dates AS `to`
FROM mytable AS t1
JOIN mytable AS t2
  ON t1.the_dates < t2.the_dates
LEFT JOIN mytable AS t3
  ON t3.the_dates > t1.the_dates 
   AND t3.the_dates < t2.the_dates
WHERE t3.the_dates IS NULL
  AND t2.the_dates > DATE_ADD(t1.the_dates, INTERVAL 5 MINUTE)
ORDER BY t1.the_dates;