Filter rows on difference between timestamp field
Hi to all,
I have table like this:
RCD_ID house_id log_timestamp
-------|-----------|---------------------
1, 2737738826, '2015-11-02 14:30:01'
3, 2737738826, '2015-11-02 14:35:12'
6, 2737738826, '2015-11-02 14:45:49'
8, 2737738826, '2015-11-02 14:51:16'
10, 2737738826, '2015-11-02 14:56:29'
12, 2737738826, '2015-11-02 15:01:43'
14, 2737738826, '2015-11-02 15:06:56'
16, 2737738826, '2015-11-02 15:12:11'
18, 2737738826, '2015-11-02 15:17:26'
20, 2737738826, '2015-11-02 15:22:42'
22, 2737738826, '2015-11-02 15:27:57'
24, 2737738826, '2015-11-02 15:33:13'
26, 2737738826, '2015-11-02 15:38:14'
28, 2737738826, '2015-11-02 15:43:58'
And I need to filter it to have each row "distanced" by 20 minutes like this:
RCD_ID house_id log_timestamp
-------|-----------|---------------------
1, 2737738826, '2015-11-02 14:30:01'
8, 2737738826, '2015-11-02 14:51:16'
10, 2737738826, '2015-11-02 14:56:29'
20, 2737738826, '2015-11-02 15:22:42'
28, 2737738826, '2015-11-02 15:43:58'
I tried the following but with no success:
select m.*,
(select m2.rcd_id
from tbl_houses_data m2
where (m2.rcd_id = m.rcd_id) AND (TIMESTAMPDIFF(MINUTE, m.log_timestamp, m2.log_timestamp) = 30)
order by m2.house_id limit 1) as m2
from tbl_houses_data as m
Could someone give me some suggestions?
Regards,
Daniele.