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

create yearly laps on SQL – Postgresql

I have 3 columns as user_id, start_date, end_date. as an example;

user_id     start_date    end_date
  1         2022-07-30    2025-07-30
  2         2022-05-25    2027-05-25

I want to create rows based on start_date and end_date with yearly laps on the same date. so it should be like as below;

user_id     start_date    end_date
  1         2022-07-30    2023-07-30
  1         2023-07-30    2024-07-30
  1         2024-07-30    2025-07-30

  2         2022-05-25    2023-05-25
  2         2023-05-25    2024-05-25
  2         2024-05-25    2025-05-25
  2         2025-05-25    2026-05-25
  2         2026-05-25    2027-05-25

how can I do that?

Some remarks:
I’m using AWS Redshift environment. I couldn’t use recursive CTE since it is in the middle of a very long query. If I’m not wrong recursive CTEs has to start with ‘WITH’ clause.

edit: thanks for all the answers and they are answering the specific request above but I miss a very important part(it was only user 1). for every user end date is different. For above example it is 2 years but for some users it is 5 years in between. so it should be dynamic, I added another example.

Tags:

3

Answers


  1. 0

    Something like:

    SELECT
    *
FROM (
    SELECT
        user_id
        , (start_date + (i * interval '1 year'))::date AS new_start_date
        , (end_date + (i * interval '1 year'))::date AS new_end_date
    FROM
        some_table
        , generate_series(0, 2) i
    ) AS tripled

    A CTE is (or can be) basically just a sub-query.

    OK, so with the changed requirements

    SELECT
    user_id
    , d::date AS year_date
FROM
    some_table
    , generate_series(start_date, end_date, '1 year') d

    This form of generate_series produces a timestamptz so you need to cast it back to a date.

    Login or Signup to reply.
  2. 0 
    SELECT
    ('2022-07-30'::date + (yr || ' year')::interval)::date AS start_date,
    ('2023-07-30'::date + (yr || ' year')::interval)::date AS end_date
FROM
    generate_series(0, 2) AS yr;

 start_date |  end_date  
------------+------------
 07/30/2022 | 07/30/2023
 07/30/2023 | 07/30/2024
 07/30/2024 | 07/30/2025

    UPDATED

    Cover variable length of years for initial start and end values. This assumes that each start/end period is always going to be 1 year in length.

    
SELECT
    start_date::date,
    (start_date + '1 year'::interval)::date AS end_date
FROM
    generate_series('2022-05-25'::date, ('2027-05-25'::date - '1 year'::interval), '1 year') AS start_date;

 start_date |  end_date  
------------+------------
 05/25/2022 | 05/25/2023
 05/25/2023 | 05/25/2024
 05/25/2024 | 05/25/2025
 05/25/2025 | 05/25/2026
 05/25/2026 | 05/25/2027
    Login or Signup to reply.
  3. 0

    first filter out rows that ‘MM-DD’ (month,day) are the same.
    then for the each matched remaining rows, duplicate (end_date’s year – start_date’s year + 1 row.
    It’s a common table expression (CTE), but it’s not recursive.CTE tend to be more readable.

    BEGIN;
CREATE temp TABLE test_gap (
    user_id bigint,
    start_date date,
    end_date date
) ON COMMIT DROP;

INSERT INTO test_gap
    VALUES (1, '2022-07-30', '2023-07-30'),
    (2, '2022-07-30', '2024-07-30'),
    (3, '2019-07-10', '2023-07-20');

WITH cte1 AS (
    SELECT
        *,
        EXTRACT(year FROM end_date) - EXTRACT(year FROM start_date) + 2 AS freq
    FROM
        test_gap
    WHERE
        EXTRACT(MONTH FROM start_date)::text || EXTRACT(day FROM start_date)::text 
        = EXTRACT(MONTH FROM end_date)::text || EXTRACT(day FROM end_date)::text
)
SELECT
    user_id
    ,(start_date + interval '1 year' * s)::date
    ,(start_date + interval '1 year' * (s+1))::date
FROM
    cte1
    CROSS JOIN generate_series(1, freq) s;
END;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search