How to create a MySQL query for time based elements with a 'safe window'?
- by pj4533
I am no SQL expert, far from it. I am writing a Rails application, and I am new at that as well. I come from a desktop programming background.
My application has a table of data, one of the columns is the time at which the data was logged. I want to create a query with a 'safe window' around EACH row. By that I mean, it returns the first row, then for X minutes (based on the timelogged column) it won't return any data, once X minutes is up, it will return the next row.
For example:
ID | TimeLogged
1 | 3/5/2010 12:01:01
2 | 3/5/2010 12:01:50
3 | 3/5/2010 12:02:03
4 | 3/5/2010 12:10:30
5 | 3/5/2010 01:30:03
6 | 3/5/2010 01:31:05
With a 'safe window' of 5 minutes I want to create a query to return:
1 | 3/5/2010 12:01:01
4 | 3/5/2010 12:10:30
5 | 3/5/2010 01:30:03
(It skipped the 12:01:50 and 12:02:03 items because they occurred within 5 minutes of the first item.)
Another example, with a 'safe window' of 15 minutes I want to return:
1 | 3/5/2010 12:01:01
5 | 3/5/2010 01:30:03
Perhaps I have to just return all data and parse it myself?