i have a table
and i have a range from '2019-01-02' to '2019-01-04'
I need to generate ID and DATES (generated) from my table which started_at and ended_at (nullable) between the given range
result must be like this:
ID 4 from table is not included in result because it's started_at and ended_at not in range '2019-01-02' and '2019-01-04'
I need query that will do that in postgres.
Use generate_series()
select t.id, g.dt::date
from the_table t
cross join generate_series(t.started_at::date + 1,
least(t.ended_at::date, date '2019-01-04'),
interval '1 day') as g(dt)
where t.started_at >= date '2019-01-02'
and t.started_at < date '2019-01-04';
Worked this variant:
select t.id, g.dt::date from the_table t
cross join generate_series(t.started_at::date + 1,
least(t.ended_at::date, date '2019-01-04'), interval '1 day') as g(dt)
where dt >= date '2019-01-02' and dt < date '2019-01-04';