Question 352687 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL: Retrieval of events between two dates, based on the event's starting date and repetition interval

Good morning, starting from the following database table

ID start end each_month
1 2024-03-03 2030-01-01 2
2 2024-04-03 2030-01-01 1

I need a query that, starting from this row, returns the following resultset, if I search events between 2024-02-01 and 2024-07-31 will returns the following (calculated) rows

ID start
1 2024-03-03
1 2024-05-03
1 2024-07-03
2 2024-04-03
2 2024-05-03
2 2024-06-03
2 2024-07-03

Thank you all for helping me.

Tags:

2

Answers


  1. 0

    Unfortunately, your description is very imprecise and leaves a lot of room for interpretation. Could you please formulate this a little more precisely, in particular how the calculation should work exactly.

    I don’t think you will be able to solve this problem with SQL alone. You could perhaps try a stored procedure. However, it is probably a better approach not to execute the calculation routine directly on the database.

    Translated with DeepL.com (free version)

    Login or Signup to reply.
  2. 0

    You use a recursive CTE to calculate the repeated dates up through the end of your search range, then select from that:

    with recursive expanded_events as (
    select id, start as orig_start, end, each_month,
        start, 1 as counter
    from events
    where start <= '2024-07-31'

    union all

    select id, orig_start, end, each_month,
        orig_start + interval counter*each_month month, counter+1
    from expanded_events
    where
        orig_start + interval counter*each_month month <= least(end,'2024-07-31')
)
select id, start
from expanded_events
where start between '2024-02-02' and '2024-07-31'
order by id, start

    (Note that with adding months to dates it is important to use a separate counter, not iteratively add a number of months, otherwise you will get incorrect results when the day does not exist in all months.)

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search