Postgres SQL multiple outer joins to generate time series

Question

I am trying to generate a series of 48 periods x days into the future, and displaying matching data from a source table (T1) where available, however this just seems to act like an inner join and only displays rows which have a matching results from T1?

select
seq.date,
t1.date,
hh.period,
t1.period
From

myTable t1
right outer  join  (select date(date) from generate_series(current_date,current_date + '12 days'::interval ,'1 day'::interval) date)as  seq 
 ON (seq.date= t1.date)
right outer join (select period from generate_series (1,48) period) hh 
ON (hh.period = t1.period)

David דודו Markovitz · Accepted Answer · 2016-12-18 18:00:23Z

1

Cross join the series and then left join the table

Cellphone...

select seq.date, t1.date, hh.period, t1.period From (select date(date) 
from generate_series(current_date,current_date + '12 days'::interval ,'1 day'::interval) date)as seq 
cross join (select period from generate_series (1,48) period) hh
Left join my table
 ON (hh.period = t1.period)
And (seq.date= t1.date)

edited Dec 18, 2016 at 18:00

answered Dec 18, 2016 at 17:49

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

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

Collectives™ on Stack Overflow

Postgres SQL multiple outer joins to generate time series

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related