1

(edited - I oversimplified the original problem)

I'm trying to figure out a MySQL query that will collapse one-day long contiguous start/end date ranges. Here is my sample input data:

INSERT INTO my_table(tag, start_date, end_date) VALUES ('AA', '2020-07-01', '2020-07-02');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('XB', '2020-07-02', '2020-07-03');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('YC', '2020-07-03', '2020-07-04');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('ZD', '2020-07-04', '2020-07-05');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('0E', '2020-07-05', '2020-07-06');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-06', '2020-07-15');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-20', '2020-07-21');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-21', '2020-07-22');
INSERT INTO my_table(tag, start_date, end_date) VALUES ('CA', '2020-07-22', '2020-07-23');

INSERT INTO my_table(tag, start_date, end_date) VALUES ('EA', '2020-07-23', '2020-07-31');

Here is the output I want:

AA,2020-07-01,2020-07-06
CA,2020-07-06,2020-07-15
CA,2020-07-20,2020-07-23
EA,2020-07-23,2020-07-31

It seems like it should be simple, but I can't figure out how to do it in pure MySQL. I've tried joining the table to itself and using various combinations of MIN() and MAX(), but the solution eludes me. I can do it in PHP code, but would prefer to let MySQL do the work.

I can't rely on the tag field for grouping (it can be anything). The date collapse should be done only on the date ranges that are 1-day long and contiguous. Date ranges longer than one day should not be collapsed. The selected tag should be the value associated with the first date in the range

5
  • o_O SELECT tag, MIN(start_date) start_date, MAX(end_date) end_date FROM my_table GROUP BY tag;.
    – Akina
    Jun 3, 2020 at 4:40
  • Thanks for the reply. Your answer works for the original question, but I oversimplified things and my actual problem needs to work with different tag values. I cannot simply group by the tag field, I need to collapse things based only on contiguous dates.
    – lfjeff
    Jun 3, 2020 at 5:11
  • SELECT ANY_VALUE(tag) tag, MIN(start_date) start_date, MAX(end_date) end_date FROM my_table GROUP BY LEFT(tag, 1);. If you need the tag value taken from the most ancient row strictly then specify your MySQL version.
    – Akina
    Jun 3, 2020 at 5:23
  • Sorry, I hadn't finished editing the problem to be more specific in what I'm trying to do.
    – lfjeff
    Jun 3, 2020 at 5:26
  • On new version of the task the version of MySQL is critical - specify it.
    – Akina
    Jun 3, 2020 at 5:29

1 Answer 1

1

After a little more research, I figured out the solution to this "gaps and islands" problem:

SELECT t.tag, begin AS start_date, MAX(end) AS end_date
FROM (
    SELECT
    a.*
    CASE WHEN (DATEDIFF(start_date, @prev_date) = 1 AND DATEDIFF(end_date, start_date) = 1)
        THEN @begin := @begin
        ELSE @begin := start_date
    END AS begin,
    end_date AS end,
    @prev_date := start_date
    FROM (SELECT @prev_date := NULL, @begin := NULL) AS vars
    JOIN my_table a
    ORDER BY start_date
) AS t
GROUP BY begin;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.