MySQL Forums
Forum List  »  Newbie

Filter rows on difference between timestamp field
Posted by: Daniele Barzotti
Date: November 02, 2015 11:16AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Filter rows on difference between timestamp field
November 02, 2015 11:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.