Find a date exactly after a month, after two months, after 3 months etc-CodePudding

I have a dataset of users' daily status, and I want to find a user's status after each month.

For ex: If a user has his daily status from 09/20/2020 to 03/10/2022. I want to find the status on the following days:

Desired Output:

Date	Status
09/20/2020	Yes
10/20/2020	No
11/20/2020	Yes
12/20/2020	Yes

CodePudding user response：

Your questions a little unclear but you may want to look at using a for loop and the DATEADD(MONTH) functions

CodePudding user response：

CREATE temp TABLE user_status (
    userid bigint DEFAULT 1,
    status_date date,
    status boolean
);

INSERT INTO user_status (status_date, status)
    VALUES ('2020-09-20', TRUE), ('2020-09-21', FALSE), ('2020-09-29', TRUE), ('2020-10-20', FALSE), ('2020-10-22', FALSE), ('2020-10-29', TRUE), ('2020-11-20', TRUE), ('2020-11-24', FALSE), ('2020-11-29', TRUE), ('2020-12-20', TRUE), ('2020-12-24', FALSE), ('2021-11-29', TRUE);

then:

SELECT
    d::date,
    status
FROM
    generate_series('2020-09-20'::timestamp, '2020-12-20'::timestamp, interval '1 month') d
    LEFT JOIN user_status ON status_date = d
WHERE
    status_date > '2020-09-19'
    AND status_date < '2022-3-10';

reference: https://www.postgresql.org/docs/current/functions-srf.html