1

I have a time series table with values every 5 minute, with the following table structure:

DATETIME              VALUE
2000-01-01 00:00:00   4
2000-01-01 00:05:00   33
2000-01-01 00:10:00   8
2000-01-01 00:15:00   9
2000-01-01 00:25:00   13
2000-01-01 00:30:00   98
2000-01-01 00:35:00   23
2000-01-01 00:40:00   22

I want to identify all the places where there is a gap in the time series (i.e.: Line 5 in this example, there is no value for 2000-01-01 00:20:00).

Any help is greatly appreciated!

Vincent L
  • 699
  • 2
  • 11
  • 25
  • You can use OUTER SELF JOIN. – PM 77-1 Jan 09 '17 at 01:12
  • Thanks for your answer. How would that work exactly? I need to indentify where two records have more than 5 minutes difference between the two – Vincent L Jan 09 '17 at 01:16
  • You need to identify records that do not have "+5 minutes" counterparts. – PM 77-1 Jan 09 '17 at 01:20
  • I understand that, but I cannot figure out what the syntax would be like. Can you please elaborate? Thank you. – Vincent L Jan 09 '17 at 01:50
  • Actually, I wrote a little c# console app that connects to the DB, fetch all the data and loops at the array to check. Takes a couple seconds to run and does the job. I know it's not answering my own question on how to do it in MySQL, but I feel like this is much more performant than anything I could have done in MySQL purely. – Vincent L Jan 09 '17 at 03:52
  • I doubt it's 'more' performant- but at 2 secs, who cares ;-) – Strawberry Jan 09 '17 at 04:07
  • For a table with 2.5 million records, I doubt it would have taken less than 2 seconds in MySQL – Vincent L Jan 09 '17 at 04:09

0 Answers0