MySQL: Select rows that are in a date range of each other

Question

I have a MySQL like the following:

Events
+----+------+--------------------------------+
| id | name | sites_id | created             |
+----+------+--------------------------------+
| 1  | test | 1        | 2013-11-01 00:00:00 |
| 2  | test | 1        | 2013-11-02 00:00:00 |
| 3  | test | 2        | 2013-11-13 00:00:00 |
| 4  | test | 3        | 2013-11-14 00:00:00 |
| 5  | test | 4        | 2013-11-25 00:00:00 |
+----+------+----------+---------------------+

What I want to select events that are created with in 48 hours of each other and have the same site id. (in this example I would expect ids 1 and 2).

Any help at all would be appreciated as I have drawn a blank how to do this solely in SQL.

Thanks

Answer 1

Try something like this:

SELECT DISTINCT e1.*
FROM events e1
INNER JOIN events e2 ON e1.sites_id = e2.sites_id
  AND e1.id <> e2.id
WHERE ABS(datediff(e1.created, e2.created)) <= 2;

sqlfiddle demo

This gives you the result:

ID  NAME    SITES_ID    CREATED
2   test    1           November, 02 2013 00:00:00+0000
1   test    1           November, 01 2013 00:00:00+0000

Answer 2

I've not syntax checked this (so see it as more of a guideline):

SELECT DISTINCT
    t1.id
    , t1.name 
    , t1.sites_id 
    , t1.created
FROM
    Events t1
    INNER JOIN Events t2
        ON (t2.created BETWEEN DATE_ADD(t1.created, INTERVAL -2 DAY) AND DATEADD(DAY, INTERVAL 2 DAY))
        AND t1.sites_id = t2.sites_id
        AND t1.id <> t2.id