PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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
Tags:

2

Answers


  1. 0

    You need to change only the intervall and remove one month see examples, to get the complete set of moth,

    Adding a send cte to grab the last Month, which you need to differentitiate beween the last month and the rest

    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

    fiddle

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search