Sample Header Ad - 728x90

Weed out certain rows where timestamp is within interval

1 vote
1 answer
986 views
Given a table structure: create table contacts ( contact_id int(8) not null auto increment, #primary key pid int(8) not null, #personal id contakt_time datetime, #time of contact ) Where each person can have multiple contacts with the system at any point in time. What is the best way to filter out contacts by a given person in a certain time frame? E.g. if: SELECT * FROM contacts where pid = 1201; yields: contact pid contact_time ----------------------------------- 10530 1201 2014-01-04 14:33:00 11475 1201 2014-01-13 18:16:00 13460 1201 2014-02-03 12:23:00 14220 1201 2014-02-10 16:55:10 15599 1201 2014-02-24 21:03:34 16488 1201 2014-03-06 12:45:00 16499 1201 2014-03-06 14:55:00 16900 1201 2014-03-10 11:18:00 19108 1201 2014-04-02 10:40:00 20954 1201 2014-04-22 17:24:00 I would need to remove all contacts except the first one in a given time window. For instance, all contacts happening within a 5 hours window after each contact. In the example above, the result would no longer include contact #16499 as this is too close to contact #16488 (< 5 hours apart). Thank you for any help
Asked by tschmitty (11 rep)
Jul 6, 2015, 11:25 AM
Last activity: Feb 16, 2025, 12:01 PM