A query for getting results separated by a date gap

ID	TIMESTAMP
1	2020-01-01 12:00:00
2	2020-02-01 12:00:00
3	2020-05-01 12:00:00
4	2020-06-01 12:00:00
5	2020-07-01 12:00:00

---

I am looking for a way to get records in a MySQL database that are within a certain range of each other. In the above example, notice that there is a month between the first two records, then a three month gap, before we see another three records with a month between.

What is a way to group these into two result sets, so I will get Ids 1, 2 and 3, 4, 5 A solution using days would be probably work the best as thats easier to modify.

Answer

You can use lag() and then logic to see where a gap is big enough to start a new set of records. A cumulative sum gives you the groups you want:

select t.*,
       sum(case when prev_timestamp >= timestamp - interval 1 month then 0 else 1 end) over (order by timestamp) as grouping
from (select t.*,
             lag(timestamp) over (order by timestamp) as prev_timestamp
      from t
     ) t;

If you want to summarize this with a start and end date:

select min(timestamp), max(timestamp)
from (select t.*,
             sum(case when prev_timestamp >= timestamp - interval 1 month then 0 else 1 end) over (order by timestamp) as grouping
      from (select t.*,
                   lag(timestamp) over (order by timestamp) as prev_timestamp
            from t
           ) t
      ) t
group by grouping;