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
Last activity: Feb 16, 2025, 12:01 PM