How to generate a series but keep the start and end date?

Edited* Using psql 9.4.26

I have this table:

And using psql, I'm trying to get an output similar to:

I queried the following:

WITH date_ranges AS (
    SELECT
        TO_DATE("Start", 'DD/MM/YYYY') AS start_date,
        TO_DATE("End", 'DD/MM/YYYY') AS end_date
    FROM
      tabl
)
SELECT
    TO_CHAR(month_start, 'DD/MM/YYYY') AS Start,
    TO_CHAR(LEAST(month_start + INTERVAL '1 month' - INTERVAL '1 day', end_date), 'DD/MM/YYYY') AS End
FROM
    date_ranges
CROSS JOIN LATERAL generate_series(
    date_trunc('month', start_date),
    LEAST(date_trunc('month', end_date) + INTERVAL '1 month' - INTERVAL '1 day', end_date),
    INTERVAL '1 month'
) AS month_start(month_start);

However, the first record output is this:

The outcome should be:

Answers

To achieve the desired output where the start date begins from the actual start date in the table rather than the first day of the month, you can adjust the generate_series function accordingly. Here's the modified query:

WITH date_ranges AS (
    SELECT
        TO_DATE("Start", 'DD/MM/YYYY') AS start_date,
        TO_DATE("End", 'DD/MM/YYYY') AS end_date
    FROM
      tabl
)
SELECT
    TO_CHAR(start_date, 'DD/MM/YYYY') AS Start,
    TO_CHAR(LEAST(start_date + INTERVAL '1 month' - INTERVAL '1 day', end_date), 'DD/MM/YYYY') AS End
FROM
    date_ranges
UNION ALL
SELECT
    TO_CHAR(month_start, 'DD/MM/YYYY') AS Start,
    TO_CHAR(LEAST(month_start + INTERVAL '1 month' - INTERVAL '1 day', end_date), 'DD/MM/YYYY') AS End
FROM
    date_ranges
CROSS JOIN LATERAL generate_series(
    date_trunc('month', start_date + INTERVAL '1 month'),
    LEAST(date_trunc('month', end_date) + INTERVAL '1 month' - INTERVAL '1 day', end_date),
    INTERVAL '1 month'
) AS month_start(month_start);

In this modified query:

1. I've added a UNION ALL clause to include the initial period from the actual start date to the end of the first month.
2. The generate_series function now starts from the beginning of the second month onwards to ensure that the first period starts from the actual start date in the table.
3. The start_date is used as the start date for the first period instead of the truncated month.