Jarfis Jarfis - 16 days ago 5
MySQL Question

Mysql - most recent record that was created over N days after the record before it

I don't know if this is possible or not, but here's what I'm looking for

I need the current date if the last record created is more than N days old OR the created_at of the first record created over N days after the created_at date of the first record created before it

So, like, if there exist 2 records that have creation dates with a difference greater than N days and no other records created between those two dates, give me the created_at of the most recently created of those records unless the most recent created_at of all records is greater than N days ago, in which case give today's date instead

so given this table and N = 5

+----+-------------+
|id |created_at |
+----+-------------+
|9 |1 day ago |
|8 |2 days ago |
|7 |3 days ago |
|6 |4 days ago |
|5 |5 days ago | < not this date
|4 |6 days ago | < get this date
|3 |12 days ago | < not this date
|2 |13 days ago | < not this date
|1 |20 days ago |
+----+-------------+


I need to get
6 days ago
but not
13 days ago
or
5 days ago
, and if the records looked like this

+----+-------------+
|id |created_at |
+----+-------------+
|5 |11 day ago | < not this date, instead today's date
|4 |12 days ago |
|3 |20 days ago |
|2 |21 days ago |
|1 |30 days ago |
+----+-------------+


I need today's date

Answer

Start with a query that calculates the gap between adjacent dates:

SELECT created_at, DATEDIFF(@prevdate, created_at) AS diff, @prevdate := created_at
FROM (SELECT created_at
      FROM yourTable
      WHERE created_at <= DATE_SUB(CURDATE(), INTERVAL @N DAY)
      ORDER BY created_at DESC) AS x,
CROSS JOIN (SELECT @prevdate := NULL) as vars

Then add a filter for rows where diff is more than N, and select the most recent of these

SELECT MAX(created_at) AS selected_date
FROM (SELECT created_at, DATEDIFF(@prevdate, created_at) AS diff, @prevdate := created_at
      FROM (SELECT created_at
            FROM yourTable
            WHERE created_at <= DATE_SUB(CURDATE(), INTERVAL @N DAY)
            ORDER BY created_at DESC) AS x
      CROSS JOIN (SELECT @prevdate := NULL) as vars) AS z
WHERE diff > @N;

Finally, to get the current date if the most recent date is more than N days ago, you can join with another query that gets the most recent date, and use IF().

SELECT IF (latest_date < DATE_SUB(CURDATE(), INTERVAL @N DAY), CURDATE(), MAX(created_at)) AS selected_date
FROM (SELECT created_at, DATEDIFF(@prevdate, created_at) AS diff, @prevdate := created_at
    FROM (SELECT created_at
          FROM yourTable
          WHERE created_at <= DATE_SUB(CURDATE(), INTERVAL @N DAY)
          ORDER BY created_at DESC) AS x
    CROSS JOIN (SELECT @prevdate := NULL) as vars) AS z
CROSS JOIN (SELECT MAX(created_at) AS latest_date FROM yourTable) AS y
WHERE diff > @N;

DEMO