mysqlsqlsqlitepandasql

Get list of dates from two dates columns in sqlite3 (pandasql)


I have a date_from and date_to . I want to get the list of dates in between these two dates, grouped by version.

version date_from date_to
ver1 2020-01-05 2020-01-07
ver2 2021-05-09 2021-05-11
ver1 2022-02-05 2022-02-07

Result I want

version date
ver1 2020-01-05
ver1 2020-01-06
ver1 2020-01-07
ver1 2022-02-05
ver1 2022-02-06
ver1 2022-02-07
ver2 2021-05-09
ver2 2021-05-10
ver2 2021-05-11

I tried that recursive query but it didn't work properly :(

WITH RECURSIVE dates(Date) AS
   (
       SELECT date_from from df as Date
       UNION ALL
       SELECT date(date, '+1 day') FROM dates WHERE Date < (Select date_to from df)
   )
   SELECT DATE(Date) FROM dates; 

Solution

  • You can use something like following -

    with recursive cte (version,date_from,date_to, next_date) as
    (select version, date_from, date_to,date_from from gen_dt
    union all
    select version, date_from, date_to, date(next_date, '+1 day')
    from cte c
    where next_date < date_to
    )select version, next_date from cte order by version, next_date;
    

    Fiddle here.