skip to Main Content

i have a table like bellow in postgres

start_at end_at
17/1/2023 15/3/2023

i want to split this like bellow

start_at end_at
17/1/2023 31/1/2023
01/2/2023 28/2/2023
1/3/2023 15/3/2023

I have tried generate series of postgres but not able to generate desire row. Please someone help me

2

Answers


  1. Recommended Solution(h/t: Prosenjit Chongder):

    The solution using generate_series function is much more compact.

    SELECT TO_CHAR(GREATEST(y, c.start_at), 'DD/MM/YYYY') AS start_at,
           TO_CHAR(LEAST(y + INTERVAL '1 month' - INTERVAL '1 day', c.end_at), 'DD/MM/YYYY') AS end_at
    FROM date_ranges c, 
         generate_series(date_trunc('month', c.start_at::timestamp), date_trunc('month', c.end_at::timestamp), INTERVAL '1 month') y
    ORDER BY GREATEST(y, c.start_at), LEAST(y + INTERVAL '1 month' - INTERVAL '1 day', c.end_at);
    

    Fiddle


    Older Solution:

    You will need a recursive Common table expression to acheive your purpose.

    WITH RECURSIVE date_split(start_at, end_at, current_start, current_end) AS (
      SELECT start_at, end_at,
             start_at, LEAST(end_at, (date_trunc('MONTH', start_at) + INTERVAL '1 MONTH' - INTERVAL '1 day')::DATE)
      FROM date_ranges
      WHERE start_at <= end_at
      UNION ALL
      SELECT start_at, end_at,
             (current_end + INTERVAL '1 day')::DATE,
             LEAST(end_at, (date_trunc('MONTH', current_end + INTERVAL '1 day') + INTERVAL '1 MONTH' - INTERVAL '1 day')::DATE)
      FROM date_split
      WHERE current_end < end_at
    )
    SELECT TO_CHAR(current_start, 'DD/MM/YYYY') AS start_at, TO_CHAR(current_end, 'DD/MM/YYYY') AS end_at
    FROM date_split
    ORDER BY current_start;
    

    outputs:

    start_at    end_at
    17/01/2023  31/01/2023
    01/02/2023  28/02/2023
    01/03/2023  15/03/2023
    

    DBFiddle

    Login or Signup to reply.
  2. The following will generate the described results:

    WITH parms AS (SELECT '2023-01-17'::date AS start_at, '2023-03-15'::date AS end_at)
    SELECT greatest(parms.start_at, g.month_start)::date AS start_at,
           least(parms.end_at, g.month_start + interval '1' MONTH - interval '1' DAY)::date AS end_at
    FROM parms
    CROSS JOIN LATERAL generate_series(date_trunc('month',parms.start_at), parms.end_at, interval '1' MONTH) g(month_start)
    ORDER BY start_at;
    

    Results:

    start_at end_at
    2023-01-17 2023-01-31
    2023-02-01 2023-02-28
    2023-03-01 2023-03-15
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search