By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select min(dte), max(dte)
from generate_series('2019-09-15'::date, '2021-09-21'::date, interval '1 day') gs(dte)
group by date_trunc('year', dte);
| min | max |
|---|---|
| 2021-01-01 00:00:00+00 | 2021-09-21 00:00:00+01 |
| 2019-09-15 00:00:00+01 | 2019-12-31 00:00:00+00 |
| 2020-01-01 00:00:00+00 | 2020-12-31 00:00:00+00 |
select greatest(gs.dte, v.fromdate), least(gs.dte + interval '1 year - 1 day', v.todate)
from (values ('2019-09-15'::date, '2021-09-21'::date)) v(fromdate, todate) cross join lateral
generate_series(date_trunc('year', v.fromdate), date_trunc('year', v.todate), interval '1 year') gs(dte)
| greatest | least |
|---|---|
| 2019-09-15 00:00:00+01 | 2019-12-31 00:00:00+00 |
| 2020-01-01 00:00:00+00 | 2020-12-31 00:00:00+00 |
| 2021-01-01 00:00:00+00 | 2021-09-21 00:00:00+01 |