How to get the set of dates between date_from and date

I have a table:

good_id	from_date	to_date
1	2021-10-01	2021-10-03

I want to get a data table like this:

good_id	all_date
1	2021-10-01
1	2021-10-02
1	2021-10-03

I tried using Cross Join with an all_date table containing all the dates in October. but it didn't work. Do you have any ideas for this problem?

CodePudding user response：

Actually, the solution for your problem typically is done in the other direction. We usually start with a calendar table looking like:

dates (dt)
----------
2021-10-01
2021-10-02
2021-10-03

And then left join this table to your table containing the date ranges, e.g.

SELECT d.dt
FROM dates d
LEFT JOIN yourTable t
    ON d.dt BETWEEN t.from_date AND t.to_date;

Note that SQL usually is not so good as generating new data. Mainly, it is used for extracting or altering data which already exists. Using a calendar table as shown above is a standard way of handling your problem. In practice, you might include more dates to cover whatever data you expect in your table.