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

Question

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;

Pankaj · Accepted Answer · 2022-07-06 16:09:47Z

0

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.

answered Jul 6, 2022 at 16:09

Pankaj

2,7762 gold badges9 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pankaj Over a year ago

Please feel free to accept/upvote in case this solution worked for you.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related