Generate dates starts with last day of a month and ends with first day of a month

Question

I have dates always with 1st day of the month like: 2022-11-01 and 2022-01-01. I want to get dates with last days of the month (so it will be 2022-11-30 and 2022-10-31) etc and then subtract 3 months from these dates.

So for date 2022-11-01 I want to see array with dates from 2022-09-01 to 2022-11-30 and from 2021-10-01 to 2022-01-31:

I expect table like this:

new_date
2022-11-30
2022-11-29
2022-11-28
...
2022-09-03
2022-09-02
2022-09-01

I tried with:

WITH subtractDays(addMonths(toDate('2022-11-01'), 1),1) as start
SELECT subtractDays(toDate(start), number) AS new_date
FROM (
    SELECT arrayJoin(range(0, 90)) AS number)
    

But I get 2022-09-02 at the end. It happens because inside arrayJoin I use 90 days, so this thing doesnt understand that month can have 31, 30, 29 days.. How can I fix it?

Answer 1

Try this way:

SELECT arrayJoin(data_range) AS new_date
FROM 
(
    WITH toDate('2022-11-01') AS dt
    SELECT
        dt,
        addMonths(dt, -2) AS from,
        addMonths(dt, 1) AS to,
        arrayMap(x -> (from + x), range(toUInt32(to - from))) AS data_range
)

/*
┌───new_date─┐
│ 2022-09-01 │
│ 2022-09-02 │
...
│ 2022-11-29 │
│ 2022-11-30 │
└────────────┘
*/