Given min and max dates are:
Min date: 2023-06-19 09:54:23.000
Max date: now()::timestamp(0)-interval '1 year'
Expected output:
start_date end_date
-------------------------------------------
2023-12-01 00:00:00.000 2023-12-09 23:59:59.999
2023-11-01 00:00:00.000 2023-11-30 23:59:59.999
2023-10-01 00:00:00.000 2023-10-31 23:59:59.999
2023-09-01 00:00:00.000 2023-09-30 23:59:59.999
2023-08-01 00:00:00.000 2023-08-31 23:59:59.999
2023-07-01 00:00:00.000 2023-07-31 23:59:59.999
2023-06-01 00:00:00.000 2023-06-30 23:59:59.999
My try:
with cte
as
(
SELECT days
from generate_series('2023-06-19 09:54:23.000',now()::timestamp(0)-interval '1 year', '1 month') days
order by days desc
)
select date_trunc('MONTH', days::date)::timestamp(0) start_date,
(date_trunc('month', days::date)::timestamp(0) + interval '1 month -1 millisecond') end_date
from cte;
Following is missing date from above query result.
start_date end_date
--------------------------------------------------
2023-12-01 00:00:00.000 2023-12-09 23:59:59.999
Why I need this date entry? - I want the current date - 1 year
date entry with start date of the month ONLY for the first entry.
The current - 1 year
date should be end date and its month start date should be start date of the serires.
If the current date is 2024-12-22
then output should be:
start_date end_date
--------------------------------------------------
2023-12-01 00:00:00.000 2023-12-22 23:59:59.999
If the current date is 2024-01-01
then output should be:
start_date end_date
--------------------------------------------------
2023-01-01 00:00:00.000 2023-01-01 23:59:59.999
Widen your generate_series()
range and use least()
to switch your final date:
demo at db<>fiddle
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 |
Use order by
only in the outer query, otherwise Postgres doesn't guarantee it'll follow the order of subqueries and CTEs.