MYSQL split date range columns into dates



  • 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


  • 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.)


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2