postgresqltimestampseriespostgresql-14

Generate timestamp series between given dates


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

Solution

  • 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.