0

I have an Event model, that has EventDates An EventDate has a from and to field. In my dashboard, if an event spans multiple days, I need to show a row for each day that event exists. I'm wondering if this behaviour is possible using MySQL only.

Here's some example data

+----+------------+------------+
| id |    from    |     to     |
+----+------------+------------+
|  1 | 09-20-2019 | 09-25-2019 |
+----+------------+------------+

I'm wondering if it's possible to create a row for each date in the range, using only MySQL? I'm using Laravel, and I could load all the results and group them, but if it's possible using MySQL only that seems like good practice.


+----+------------+
| id |    date    |
+----+------------+
|  1 | 09-20-2019 |
|  1 | 09-21-2019 |
|  1 | 09-22-2019 |
|  1 | 09-23-2019 |
|  1 | 09-24-2019 |
|  1 | 09-25-2019 |
+----+------------+
Miguel Stevens
  • 8,631
  • 18
  • 66
  • 125
  • I doubt it can be done with MySQL - but it certainly can be done with PostgreSQL like this `SELECT datum FROM generate_series(from_timestamp, to_timestamp, '1 day') AS datum`. Perhaps you can try with a recursive CTE. – IVO GELOV Jul 29 '19 at 14:44

1 Answers1

1

You can do this using mysql recursive CTE

Table Structure

CREATE TABLE dates (date1 date, date2 date);
INSERT INTO dates (date1, date2) values('2019-07-20', '2019-07-30');

Recursive CTE

WITH RECURSIVE t AS (
    SELECT date1, date2 FROM dates
    UNION ALL
    SELECT DATE_ADD(t.date1, INTERVAL 1 DAY), t.date2 
    FROM t WHERE DATE_ADD(date1, INTERVAL 1 DAY) <= date2
)
SELECT date1 FROM t;

Demo

Ashok Gadri
  • 520
  • 4
  • 11
  • Wow, that is working, that's amazing, I was discussing this solution yesterday and for some reason it only worked with hardcoded values, this is amazing! See: https://stackoverflow.com/a/57255125/1731057 – Miguel Stevens Jul 30 '19 at 07:32
  • Great.. Recursive CTE use first statement as initial statement and then execute the recursive statement until now row is returned. In second recursive statement we can use the CTE.. – Ashok Gadri Jul 30 '19 at 08:20