2

I'm learning about recursive functions, Since I need to extract a row for each day in a range of days. This is my current data

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

The goal is to receive my data as follows

+----+------------+
| 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 |
+----+------------+

I'm following an example seen here: https://stackoverflow.com/a/54538866/1731057 But for some reason my recursive function is looking for the 'cte' table.

Query 1 ERROR: Table 'project.cte' doesn't exist

WITH cte AS (
    SELECT date_from
    FROM event_dates
  UNION ALL
    SELECT DATE_ADD(event_dates.date_from, INTERVAL 1 DAY) 
    FROM cte
    WHERE DATE_ADD(event_dates.date_from, INTERVAL 1 DAY) <= event_dates.date_until
)
select * FROM cte;
Miguel Stevens
  • 8,631
  • 18
  • 66
  • 125

2 Answers2

3

The structure of your recursive CTE is off, and the upper half of the union should be a seed base case. Then, the recursive part should add one day to the previous incoming value:

WITH RECURSIVE cte (n, dt) AS (
    SELECT 1, '2019-09-20'
    UNION ALL
    SELECT n + 1, TIMESTAMPADD(DAY, n, '2019-09-20') FROM cte WHERE n <= 5
)

SELECT * FROM cte;

enter image description here

Demo

Of note, we use TIMESTAMPADD() here to get around the problem of the INTERVAL expression, which can't really take a variable.

If you want to use this approach to generate a series of dates which matches the from and to values in your table, then you can try a join:

SELECT
    t1.dt
FROM cte t1
INNER JOIN yourTable t2
    ON t1.dt BETWEEN t2.from_date AND t2.to_date;

When used this way, the recursive CTE is acting as a calendar table.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks for your time, when I replace your hardcoded '2019-09-20' with 'event_dates.date_from' I get "Unknown table 'event_dates' in field list". Any idea how I can make this variable? – Miguel Stevens Jul 29 '19 at 13:47
  • You can't really do exactly what you have in mind. The above recursive CTE is intended to be used with a constant date starting point (and an ending point in mind). _But_, we could generate a calendar table using my answer, and then left join to your table. – Tim Biegeleisen Jul 29 '19 at 13:48
  • Could you explain how to do that? – Miguel Stevens Jul 29 '19 at 13:54
  • Thanks for the addition, this still leaves 2 dates hardcoded in the cte() function? – Miguel Stevens Jul 29 '19 at 14:11
  • Yes, it does. The idea here is that you generate a date range with the CTE which is large enough to cover your entire table. – Tim Biegeleisen Jul 29 '19 at 14:13
  • So with this approach I would have to change the hardcoded dates every few years, too bad mysql doesn't have a more elegant solution for this, thanks for your insights! – Miguel Stevens Jul 29 '19 at 14:18
0

**with recursive cte as

(select id,`from` as date from date_range
union all
select id+1,date_add(date,interval 1 day)
from cte
where date<(select `to` from date_range)
)
SELECT id, date_format(date, '%d-%m-%Y') AS date
FROM cte
ORDER BY id, date;

**

Md. Nashir Uddin
  • 730
  • 7
  • 20
Tej Kiran
  • 1
  • 1