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 days::timestamp(0) AS start_date
,least( current_date+interval '-1 year +1 day -1ms'
,date_trunc('month', days)::timestamp(0)
+ interval '1 month -1 ms') AS end_date
from generate_series( date_trunc('month','2023-06-19 09:54:23.000'::date)
,current_date-interval '12 months'
,'1 month') AS days
order by start_date desc;
| 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 |
SELECT 7
--Thaw's remark
select days::timestamp(0) AS start_date
,least( date '2024-12-31'+interval '-1 year +1 day -1ms'
,date_trunc('month', days)::timestamp(0)
+ interval '1 month -1 ms') AS end_date
from generate_series( date_trunc('month','2023-06-19 09:54:23.000'::date)
,date '2024-12-31'-interval '12 months'
,'1 month') AS days
order by start_date desc;
| start_date | end_date |
|---|---|
| 2023-12-01 00:00:00 | 2023-12-31 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 |
SELECT 7
--leap year, because why not
select days::timestamp(0) AS start_date
,least( date '2026-02-28'+interval '-1 year +1 day -1ms'
,date_trunc('month', days)::timestamp(0)
+ interval '1 month -1 ms') AS end_date
from generate_series( date_trunc('month','2024-02-29 09:54:23.000'::date)
,date '2026-02-28'-interval '12 months'
,'1 month') AS days
order by start_date desc;
| start_date | end_date |
|---|---|
| 2025-02-01 00:00:00 | 2025-02-28 23:59:59.999 |
| 2025-01-01 00:00:00 | 2025-01-31 23:59:59.999 |
| 2024-12-01 00:00:00 | 2024-12-31 23:59:59.999 |
| 2024-11-01 00:00:00 | 2024-11-30 23:59:59.999 |
| 2024-10-01 00:00:00 | 2024-10-31 23:59:59.999 |
| 2024-09-01 00:00:00 | 2024-09-30 23:59:59.999 |
| 2024-08-01 00:00:00 | 2024-08-31 23:59:59.999 |
| 2024-07-01 00:00:00 | 2024-07-31 23:59:59.999 |
| 2024-06-01 00:00:00 | 2024-06-30 23:59:59.999 |
| 2024-05-01 00:00:00 | 2024-05-31 23:59:59.999 |
| 2024-04-01 00:00:00 | 2024-04-30 23:59:59.999 |
| 2024-03-01 00:00:00 | 2024-03-31 23:59:59.999 |
| 2024-02-01 00:00:00 | 2024-02-29 23:59:59.999 |
SELECT 13
--first to last
select days::timestamp(0) AS start_date
,least( date '2027-01-01'+interval '-1 year +1 day -1ms'
,date_trunc('month', days)::timestamp(0)
+ interval '1 month -1 ms') AS end_date
from generate_series( date_trunc('month','2024-12-31 09:54:23.000'::date)
,date '2027-01-01'-interval '12 months'
,'1 month') AS days
order by start_date desc;
| start_date | end_date |
|---|---|
| 2026-01-01 00:00:00 | 2026-01-01 23:59:59.999 |
| 2025-12-01 00:00:00 | 2025-12-31 23:59:59.999 |
| 2025-11-01 00:00:00 | 2025-11-30 23:59:59.999 |
| 2025-10-01 00:00:00 | 2025-10-31 23:59:59.999 |
| 2025-09-01 00:00:00 | 2025-09-30 23:59:59.999 |
| 2025-08-01 00:00:00 | 2025-08-31 23:59:59.999 |
| 2025-07-01 00:00:00 | 2025-07-31 23:59:59.999 |
| 2025-06-01 00:00:00 | 2025-06-30 23:59:59.999 |
| 2025-05-01 00:00:00 | 2025-05-31 23:59:59.999 |
| 2025-04-01 00:00:00 | 2025-04-30 23:59:59.999 |
| 2025-03-01 00:00:00 | 2025-03-31 23:59:59.999 |
| 2025-02-01 00:00:00 | 2025-02-28 23:59:59.999 |
| 2025-01-01 00:00:00 | 2025-01-31 23:59:59.999 |
| 2024-12-01 00:00:00 | 2024-12-31 23:59:59.999 |
SELECT 14
--last to first
select days::timestamp(0) AS start_date
,least( date '2027-12-31'+interval '-1 year +1 day -1ms'
,date_trunc('month', days)::timestamp(0)
+ interval '1 month -1 ms') AS end_date
from generate_series( date_trunc('month','2024-01-01 09:54:23.000'::date)
,date '2027-12-31'-interval '12 months'
,'1 month') AS days
order by start_date desc;
| start_date | end_date |
|---|---|
| 2026-12-01 00:00:00 | 2026-12-31 23:59:59.999 |
| 2026-11-01 00:00:00 | 2026-11-30 23:59:59.999 |
| 2026-10-01 00:00:00 | 2026-10-31 23:59:59.999 |
| 2026-09-01 00:00:00 | 2026-09-30 23:59:59.999 |
| 2026-08-01 00:00:00 | 2026-08-31 23:59:59.999 |
| 2026-07-01 00:00:00 | 2026-07-31 23:59:59.999 |
| 2026-06-01 00:00:00 | 2026-06-30 23:59:59.999 |
| 2026-05-01 00:00:00 | 2026-05-31 23:59:59.999 |
| 2026-04-01 00:00:00 | 2026-04-30 23:59:59.999 |
| 2026-03-01 00:00:00 | 2026-03-31 23:59:59.999 |
| 2026-02-01 00:00:00 | 2026-02-28 23:59:59.999 |
| 2026-01-01 00:00:00 | 2026-01-31 23:59:59.999 |
| 2025-12-01 00:00:00 | 2025-12-31 23:59:59.999 |
| 2025-11-01 00:00:00 | 2025-11-30 23:59:59.999 |
| 2025-10-01 00:00:00 | 2025-10-31 23:59:59.999 |
| 2025-09-01 00:00:00 | 2025-09-30 23:59:59.999 |
| 2025-08-01 00:00:00 | 2025-08-31 23:59:59.999 |
| 2025-07-01 00:00:00 | 2025-07-31 23:59:59.999 |
| 2025-06-01 00:00:00 | 2025-06-30 23:59:59.999 |
| 2025-05-01 00:00:00 | 2025-05-31 23:59:59.999 |
| 2025-04-01 00:00:00 | 2025-04-30 23:59:59.999 |
| 2025-03-01 00:00:00 | 2025-03-31 23:59:59.999 |
| 2025-02-01 00:00:00 | 2025-02-28 23:59:59.999 |
| 2025-01-01 00:00:00 | 2025-01-31 23:59:59.999 |
| 2024-12-01 00:00:00 | 2024-12-31 23:59:59.999 |
| 2024-11-01 00:00:00 | 2024-11-30 23:59:59.999 |
| 2024-10-01 00:00:00 | 2024-10-31 23:59:59.999 |
| 2024-09-01 00:00:00 | 2024-09-30 23:59:59.999 |
| 2024-08-01 00:00:00 | 2024-08-31 23:59:59.999 |
| 2024-07-01 00:00:00 | 2024-07-31 23:59:59.999 |
| 2024-06-01 00:00:00 | 2024-06-30 23:59:59.999 |
| 2024-05-01 00:00:00 | 2024-05-31 23:59:59.999 |
| 2024-04-01 00:00:00 | 2024-04-30 23:59:59.999 |
| 2024-03-01 00:00:00 | 2024-03-31 23:59:59.999 |
| 2024-02-01 00:00:00 | 2024-02-29 23:59:59.999 |
| 2024-01-01 00:00:00 | 2024-01-31 23:59:59.999 |
SELECT 36
--https://stackoverflow.com/questions/79265819/generate-timestamp-series-between-given-dates#comment139776066_79265819
--MAK: I think I got it, used this for end_date calculation
select date_trunc('MONTH', days::date)::timestamp(0) AS start_date
,case when date_trunc('month', days::date)::timestamp(0)
+interval '1 month -1 millisecond'
> now()::timestamp(0)
-interval '1 year'
then now()::timestamp(0)
-interval '1 year'
else date_trunc('month', days::date)::timestamp(0)
+interval '1 month -1 millisecond'
end as end_date
from generate_series( '2023-06-19 09:54:23.000'
,now()::timestamp(0)-interval '1 year'
,'1 month') days;
| start_date | end_date |
|---|---|
| 2023-06-01 00:00:00 | 2023-06-30 23:59:59.999 |
| 2023-07-01 00:00:00 | 2023-07-31 23:59:59.999 |
| 2023-08-01 00:00:00 | 2023-08-31 23:59:59.999 |
| 2023-09-01 00:00:00 | 2023-09-30 23:59:59.999 |
| 2023-10-01 00:00:00 | 2023-10-31 23:59:59.999 |
| 2023-11-01 00:00:00 | 2023-11-30 23:59:59.999 |
SELECT 6
--https://stackoverflow.com/a/79265845/5298879
--nbk
With cte as ( SELECT days
from generate_series('2023-06-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 CURRENT_DATE)::INTEGER
- EXTRACT(YEAR FROM days)::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 | 2025-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 |
SELECT 7