Generating dates from field on Sqlite with CTE

Question

I need a duplicated on sqlite of the generate_series() function of PostgreSQL. I have a table where I need to calculate the future payments based in 2 fields:

Date      Period
----------------
1-1-2000  60
1-2-2000  40
1-3-2000  50

So, from the first row, I need to build 60 dates starting at 1-1-2000.

I read
How to generate all dates between two dates

and discover that sqlite have CTE! But can't figure out how build the query. The samples I found have values hardcoded.

Is the desired output of the query only for a single input row? If so, how should the query identify that row? — CL., Oct 11 '17 at 07:10

score 1 · Accepted Answer · answered Oct 11 '17 at 08:59

First, you should avoid provided date format. Use one of supported ones (check SQLite Date and Time Functions).

CREATE TABLE source_table (Date, Period);
INSERT INTO source_table VALUES("2000-01-01",60),("2000-02-01",40),("2000-03-01",50);

WITH dates AS (
    SELECT * FROM source_table
    UNION -- use UNION ALL if repeated dates are desired
    SELECT DATE(JULIANDAY(Date)+1), Period-1 FROM dates WHERE Period>0
) SELECT Date FROM dates ORDER BY Date;

Generating dates from field on Sqlite with CTE

1 Answers1