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

database, mysql, sql

Solution

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

Problem

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

Original source