0

I have a table [T] with columns id,start_date, end_date. I want to get id, date, where id is split into multiple dates using start date and end date. For example: Input:

id start_date end_date
1 2022-04-03 2022-04-07
2 2022-04-11 2022-04-11

Expected Output

id date
1 2022-04-03
1 2022-04-04
1 2022-04-05
1 2022-04-06
1 2022-04-07
2 2022-04-11
3
  • 1
    What version of MySQL?
    – mustaccio
    Apr 25, 2022 at 16:40
  • The version is MySQL 8 Apr 26, 2022 at 1:21
  • You can use a recursive Common Table expression to create a domain of dates. Use min(start_date) as the base case for the recursion och increment until max(end_date). Then join this with your table using the between predicate Jul 7 at 18:58

1 Answer 1

0

Build a dates table with all possible dates.

INSERT INTO OutputTable (t_id, date)
    SELECT T.id, d.date
        FROM T
        JOIN dates AS d
        WHERE d.date BETWEEN T.start_date AND T.end_date;

(By convention id is assumed to be the PRIMARY KEY, hence unique, for a table. So, I changed to t_id for your output.)

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.