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

Postgresql – split data into month in postgres

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

Tags:

2

Answers


  1. 0

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

    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