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?.
With cte as ( SELECT days
from generate_series('2023-03-19 09:54:23.000'
,now()::timestamp(0)-interval '1 year -1 Month', '1 month') days
order by days desc)
, MAXmonth as (
SELECT
MAX(date_part('month', days)) max_month
FROM CTE)
select date_trunc('month', days) start_date ,
CASE WHEN max_month = date_part('month', days)
THEN
CURRENT_DATE
+
MAKE_INTERVAL(YEARS := EXTRACT(YEAR FROM days)::INTEGER - EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER
)
+ TIME '23:59:59.999'
ELSE
(date_trunc('month', days::date)::timestamp(0) + interval '1 month -1 millisecond') END
end_date
FROM cte CROSS JOIN MAXmonth
| start_date | end_date |
|---|---|
| 2023-12-01 00:00:00 | 2023-12-09 23:59:59.999 |
| 2023-11-01 00:00:00 | 2023-11-30 23:59:59.999 |
| 2023-10-01 00:00:00 | 2023-10-31 23:59:59.999 |
| 2023-09-01 00:00:00 | 2023-09-30 23:59:59.999 |
| 2023-08-01 00:00:00 | 2023-08-31 23:59:59.999 |
| 2023-07-01 00:00:00 | 2023-07-31 23:59:59.999 |
| 2023-06-01 00:00:00 | 2023-06-30 23:59:59.999 |
| 2023-05-01 00:00:00 | 2023-05-31 23:59:59.999 |
| 2023-04-01 00:00:00 | 2023-04-30 23:59:59.999 |
| 2023-03-01 00:00:00 | 2023-03-31 23:59:59.999 |
SELECT 10