Re: Generate a series of single days from a table of intervals.

On Wed, Jul 27, 2016 at 12:57 PM, Paul Linehan <linehanp(at)tcd(dot)ie> wrote:

>
> I wish to generate a series of dates

​https://www.postgresql.org/docs/9.5/static/functions-srf.html
​

> from these intervals and
> also to generate the intervening dates with a status of 0 (say
>

​See "LEFT JOIN"​ and "COALESCE"

>
> The output I need is:
>
> date_val status
>
> 2016-04-01 0
> 2016-04-02 1
> 2016-04-03 1
> 2016-04-04 1
> 2016-04-05 1
> 2016-04-06 0
>

> 2016-04-02 0
>

​Typo?​

> ..
> ..<status as appropriate for the rest of the month>
> ..
> 2016-04-30 0
>
>
> I would like to do this using a recursive CTE if it's the best solution,
> but I'm open to other approaches - different approaches appreciated.
>
>
​Really? That should be the last choice of execution strategy.

This isn't a tree, I'm doubtful that a recursive CTE is needed.

> CREATE TABLE testdate (datein date, dateout date, status int);
>
> INSERT INTO testdate VALUES ('2016-04-02', '2016-04-05', 1);
> INSERT INTO testdate VALUES ('2016-04-15', '2016-04-18', 1);
> INSERT INTO testdate VALUES ('2016-04-25', '2016-04-30', 1);
>

​https://www.postgresql.org/docs/9.5/static/rangetypes.html​
​https://www.postgresql.org/docs/9.5/static/functions-range.html
​

​David J.​