Weed out certain rows where timestamp is within interval 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 (&lt; 5 hours apart).

Thank you for any help
SAMPLE DATA

mysql&gt; drop database if exists tschmitty;
Query OK, 2 rows affected (0.03 sec)

mysql&gt; create database tschmitty;
Query OK, 1 row affected (0.00 sec)

mysql&gt; use tschmitty
Database changed
mysql&gt; create table contacts (
    -&gt;   contact_id int(8) not null auto_increment, #primary key
    -&gt;   pid int(8) not null,                    #personal id
    -&gt;   contact_time datetime,                  #time of contact
    -&gt;   primary key (contact_id)
    -&gt; );
Query OK, 0 rows affected (0.02 sec)

mysql&gt; insert into contacts values
    -&gt; (10530,1201,'2014-01-04 14:33:00'),
    -&gt; (11475,1201,'2014-01-13 18:16:00'),
    -&gt; (13460,1201,'2014-02-03 12:23:00'),
    -&gt; (14220,1201,'2014-02-10 16:55:10'),
    -&gt; (15599,1201,'2014-02-24 21:03:34'),
    -&gt; (16488,1201,'2014-03-06 12:45:00'),
    -&gt; (16499,1201,'2014-03-06 14:55:00'),
    -&gt; (16900,1201,'2014-03-10 11:18:00'),
    -&gt; (19108,1201,'2014-04-02 10:40:00'),
    -&gt; (20954,1201,'2014-04-22 17:24:00');
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql&gt;


HOW DATA APPEARS IN TABLE

mysql&gt; select * from contacts;
+------------+------+---------------------+
| contact_id | 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 |
+------------+------+---------------------+
10 rows in set (0.00 sec)

mysql&gt;


COLLECT ALL KEYS FOR pid 1201

mysql&gt; drop table if exists contacts_to_drop;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql&gt; create table contacts_to_drop
    -&gt; (
    -&gt;   id int not null auto_increment,
    -&gt;   contact_id int not null,
    -&gt;   contact_time datetime,
    -&gt;   keep tinyint default 1,
    -&gt;   primary key (id)
    -&gt; );
Query OK, 0 rows affected (0.05 sec)

mysql&gt; insert into contacts_to_drop (contact_id,contact_time)
    -&gt; select contact_id,contact_time FROM contacts where pid = 1201
    -&gt; order by contact_time;
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql&gt; select * from contacts_to_drop;
+----+------------+---------------------+------+
| id | contact_id | contact_time        | keep |
+----+------------+---------------------+------+
|  1 |      10530 | 2014-01-04 14:33:00 |    1 |
|  2 |      11475 | 2014-01-13 18:16:00 |    1 |
|  3 |      13460 | 2014-02-03 12:23:00 |    1 |
|  4 |      14220 | 2014-02-10 16:55:10 |    1 |
|  5 |      15599 | 2014-02-24 21:03:34 |    1 |
|  6 |      16488 | 2014-03-06 12:45:00 |    1 |
|  7 |      16499 | 2014-03-06 14:55:00 |    1 |
|  8 |      16900 | 2014-03-10 11:18:00 |    1 |
|  9 |      19108 | 2014-04-02 10:40:00 |    1 |
| 10 |      20954 | 2014-04-22 17:24:00 |    1 |
+----+------------+---------------------+------+
10 rows in set (0.00 sec)

mysql&gt;


MARK KEYS &lt; 5 HOURS (18000 SECONDS) APART

mysql&gt; update contacts_to_drop a inner join contacts_to_drop b on a.id=b.id+1
    -&gt; and UNIX_TIMESTAMP(a.contact_time) - UNIX_TIMESTAMP(b.contact_time) &lt; 18000
    -&gt; set a.keep=0;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql&gt; delete from contacts_to_drop where keep=1;
Query OK, 9 rows affected (0.00 sec)

mysql&gt; select * from contacts_to_drop;
+----+------------+---------------------+------+
| id | contact_id | contact_time        | keep |
+----+------------+---------------------+------+
|  7 |      16499 | 2014-03-06 14:55:00 |    0 |
+----+------------+---------------------+------+
1 row in set (0.00 sec)

mysql&gt; 


DELETE MARKED KEYS FROM contacts TABLE

mysql&gt; delete a.*,b.* from contacts a inner join contacts_to_drop b using (contact_id);
Query OK, 2 rows affected (0.00 sec)

mysql&gt; select * from contacts;
+------------+------+---------------------+
| contact_id | 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 |
|      16900 | 1201 | 2014-03-10 11:18:00 |
|      19108 | 1201 | 2014-04-02 10:40:00 |
|      20954 | 1201 | 2014-04-22 17:24:00 |
+------------+------+---------------------+
9 rows in set (0.00 sec)

mysql&gt;


Since contact_id 16499 is no longer in the contacts table, you can

drop table contacts_to_drop; 


and then

GIVE IT A TRY !!!

I am sure there are other methods which involve doing a DELETE JOIN of the contacts table directly against itself. I prefer not to do so.