How to create a MySQL query for time based elements with a 'safe window'?

Question

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?

Answer 1

It is possible to use GROUP BY on a column that has been rendered into fifteen minute slices, but this doesn't give you the precise control you require for this instance. The best you can hope for is sorting into separate fifteen minute buckets, but that will not guarantee a fifteen minute spacing between times. In fact, they are more likely to be closer together since 11:59:59 and 12:00:01 would be considered to be two different intervals.

Basically you're stuck using a more manual method as I don't know of any MySQL operations that will do what you want.

If you need to do this operation more than once, you may want to flag particular records in the database after you have figured out which are your designated times.