0

I'm trying to generate dates that comes after a certain start_date with the interval of a variable day/s of week.

If the start_date is 2023-01-01 and the days of the week are [2,3,5] or ['tue','thu','sat'], then it should generate the following:

2023-01-03
2023-01-05
2023-01-07

I tried running

with dates as 
(
    select 
        s.a AS dates, 
        extract (dow from s.a)::integer dw 
    from 
        'some-table' as st, 
        generate_series(start_date::timestamp, start_date::date + 365) s(a)
    where 
        st.id = 'some-value'
)
select * 
from dates 
where dw in (2,5) 
limit 10

Is there a better way to do this?

EDIT: Corrected the sample values

Zed
  • 343
  • 1
  • 2
  • 6
  • Can you elaborate the input a bit more? Allowing ['tue', 'thu', 'fri'] is dangerous. This year, 2023-01-01 was actually a Sunday, so asking for the next Tuesday should return 2023-01-03. How should we interpret the input [2,3,5]? Why would a 2 translate to 2023-01-02? Does the 2 refer to the day of the month that you want? Or you want the first Tuesday after the start date? Also, I think the DATEADD() function can be very useful regardless of the interpretation. – Mathematician 42 Jan 06 '23 at 14:31
  • @Mathematician42 You're right. Sorry, I got it mixed up. it should be 2023-01-03. the inputs [2,3,5] are days of the week where 0 = Sunday and 1 = Monday. – Zed Jan 06 '23 at 14:53
  • @Mathematician42 why does allowing ['tue', 'thu', 'fri'] dangerous? it could either be day of the week numbers or the string equivalent. I could stick to either but i'm still figuring things out so it's not final yet. I'm just trying to generate the dates – Zed Jan 06 '23 at 14:56

0 Answers0