Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


create table test_date(id bigint,start_date date, end_date date);
insert into test_date values
(100,     '2022-01-01',     '2022-04-26'),
(101,    '2022-04-20',     Null),
(102,     '2022-01-01',   '2022-02-27') ;

insert into test_date values (104,    '2022-01-20',     Null);
insert into test_date values (1010,    '2022-09-20',     Null);
3 rows affected

1 rows affected

1 rows affected

(
    SELECT
        id,
        start_date,
        end_date,
        (new_date::date + interval '1 month - 1 day')::date
    FROM
        test_date,
        generate_series((date_trunc('month', start_date)), (date_trunc('month', end_date) + interval '1 month - 1 day'), interval '1 month') g (new_date)
    ORDER BY
        id)
UNION ALL ((
        SELECT
            id,
            start_date,
            end_date,
            (date_trunc('month', start_date) + interval '1 month - 1 day')::date
        FROM
            test_date
        WHERE
            test_date.end_date IS NULL)
    UNION ALL (
        SELECT
            id,
            start_date,
            end_date,
            (date_trunc('month', (date_trunc('month', start_date) + interval '1 month - 1 day')::date) + interval '2 month - 1 day')::date
        FROM
            test_date
        WHERE
            test_date.end_date IS NULL)
    ORDER BY
        id);



id
start_date
end_date
date


100
2022-01-01
2022-04-26
2022-01-31

100
2022-01-01
2022-04-26
2022-02-28

100
2022-01-01
2022-04-26
2022-03-31

100
2022-01-01
2022-04-26
2022-04-30

102
2022-01-01
2022-02-27
2022-01-31

102
2022-01-01
2022-02-27
2022-02-28

101
2022-04-20
null
2022-04-30

101
2022-04-20
null
2022-05-31

104
2022-01-20
null
2022-01-31

104
2022-01-20
null
2022-02-28

1010
2022-09-20
null
2022-09-30

1010
2022-09-20
null
2022-10-31

id	start_date	end_date	date
100	2022-01-01	2022-04-26	2022-01-31
100	2022-01-01	2022-04-26	2022-02-28
100	2022-01-01	2022-04-26	2022-03-31
100	2022-01-01	2022-04-26	2022-04-30
102	2022-01-01	2022-02-27	2022-01-31
102	2022-01-01	2022-02-27	2022-02-28
101	2022-04-20	null	2022-04-30
101	2022-04-20	null	2022-05-31
104	2022-01-20	null	2022-01-31
104	2022-01-20	null	2022-02-28
1010	2022-09-20	null	2022-09-30
1010	2022-09-20	null	2022-10-31