I have a table like:
RCD_ID ID TIMESTAMP
--------|-----------|---------------------
1, 2737738826, '2015-10-30 16:19:17'
3, 2737738826, '2015-10-30 16:22:46'
4, 2737738826, '2015-10-30 16:24:36'
5, 2737738826, '2015-10-30 16:29:49'
7, 2737738826, '2015-10-30 16:40:02'
9, 2737738826, '2015-10-30 17:11:55'
And I need to filter the result set so each records has a 'distance' of 20 minutes each other:
RCD_ID ID TIMESTAMP
--------|-----------|---------------------
1, 2737738826, '2015-10-30 16:19:17'
7, 2737738826, '2015-10-30 16:40:02'
9, 2737738826, '2015-10-30 17:11:55'
[WRONG QUESTION: Which is the best (and fastest) way to do it?]
RIGHT QUESTION: Could someone give me some suggestion how to start from?
EDIT
I added a record_id field to the table, I try this query:
select m.*,
(select m2.rcd_id
from tbl_data m2
where (m2.rcd_id > m.rcd_id) AND (TIMESTAMPDIFF(MINUTE, m.log_timestamp, m2.log_timestamp) >= 30)
order by m2.rcd_id limit 1) as m2
from tbl_data as m
But it returns all the records. Am I on the right way?
Thanks.
Have a go at joining the table (I've used the name 'logs') to itself and only keeping those records that meet your time constraint, something like:
SELECT * FROM logs AS t1, logs AS t2 WHERE t1.ID = t2.ID AND t1.TIMESTAMP != t2.TIMESTAMP AND TIMESTAMPDIFF(MINUTE, t1.TIMESTAMP, t2.TIMESTAMP) = 20