mysql determine future dates based on records current date

Question

I researched this a lot but I couldn't find anything that comes close to my issue so I am posting scenario for assistance and guidance on find a method - just point me in right direction if possible. Unlike some comments below, I don't quite know how to provide a minimum reproducible query especially since I don't know if its even possible.

I have a mysql table that holds Event records. Each record defines when an Event is to start (startDate) - example: Sept 4, 2019 (Wednesday). The Event is supposed to repeat every Wednesday after that - but there is only the ONE master Event record. eventFreq designates how often the Event is supposed to occur.

eventFreq = 1 ; // every x day of week (every Wednesday)
eventFreq = 2 ; // every other x day of week (every other Monday)
eventFreq = 3 ; // every 3rd X day of month (every 3rd Tuesday)

Is it possible to write a mysql query that would, based on the master record startDate and the eventFreq, give me all instances of Wednesdays in a future month (IE: All Wednesdays in March 2020)? Or a query that would give me the NEXT Wednesday based on todays current date. These queries would need to figure out that startDate is a Wednesday to begin with in order to determine future ones.

If it determined a future Wednesday in the month of March 2020, it would return the master Event record plus an extra field for the specific future Wednesday.

Master Event Record: ID,eventName,eventDescription,startDate,eventFreq,blah1,blah2,blah3

If a Wednesday in March was found, it would return the entire record with an added column nextDate as one of the Wednesdays in March. thus it would return me the same four records with an appended nextDate column.

Crudely:

   IF DATE == Wednesday, then Date as nextDate  
OR IF Wednesday BETWEEN FirstDayOfMonth AND LastDayOfMonth  
OR IF Wednesday, then Next Wednesday after today

Would return the same Master record X times with an appended column nextDate :

ID,eventName,eventDescription,startDate,eventFreq,blah1,blah2,blah3,nextDate // March 4 2020
ID,eventName,eventDescription,startDate,eventFreq,blah1,blah2,blah3,nextDate // March 11 2020 
ID,eventName,eventDescription,startDate,eventFreq,blah1,blah2,blah3,nextDate // March 18 2020
ID,eventName,eventDescription,startDate,eventFreq,blah1,blah2,blah3,nextDate // March 25 2020

Currently I am extracting all Events and using PHP to process all of the Events startDate and eventFreq (every Wednesday) to determine future Wednesdays. The code is extensive and complicated for all the various scenarios - so I was hoping a good mysql query would just return the proper future dates and then all I have to do is process the results.

Can this be achieved?

Answer 1

I think you are trying to put too many things in one basket. You should separate them.

1. You can use datetime calculation functions to determine date range you are interested in

2. Then you can use one of the ways to generate a list of dates withing that date range

3. Optionally you can filter the dates by some common criteria (like excluding Sundays or public holidays if required)

4. Finnaly, you can test every date on the list if it is matching your eventFreq criteria. This can be implemented as a function, something like this:

CREATE FUNCTION `SP_IsEventFreqMatched`(
    `aDateRef` DATE,
    `aEventFreq` TINYINT UNSIGNED,
    `aDate` DATE
)
RETURNS TINYINT(1) UNSIGNED
DETERMINISTIC
BEGIN
    SET @r = DAYOFWEEK(aDate) = DAYOFWEEK(aDateRef);
    IF @r = 1 THEN
        CASE aEventFreq
            WHEN 1 THEN
                BEGIN
                    -- every x day of week (every Wednesday)
                    -- weekdays already matched. do nothing
                END;

            WHEN 2 THEN
                BEGIN
                    -- every other x day of week (every other Monday)
                    SET @r = (ABS(DATEDIFF(aDate, aDateRef)) / 7) MOD 2 = 0;
                END;

            WHEN 3 THEN
                BEGIN
                    -- every 3rd X day of month (every 3rd Tuesday)
                    SET @r = (ABS(DATEDIFF(aDate, aDateRef)) / 7) MOD 3 = 0;
                END;

            -- You can add logic other eventFreq types

            ELSE
                BEGIN
                    -- todo@ raise an error? 
                    SET @r = 0; 
                END;
        END CASE;
    END IF;

    RETURN @r;
END

So, at the end your query would look something like this (I'm using my technique for generating dates withing range, you can find it on the link provided):

SET @from = '2020-02-15';
SET @to   = '2020-03-15';

SELECT
      m.ID
    , m.startDate
    , DAYOFWEEK(m.startDate) - 1 AS weekday
    , m.eventFreq
    , d.d AS nextDate
FROM
      `global`.dates AS d
    , `Master` AS m
WHERE
        d.d BETWEEN @from AND @to
    AND SP_IsEventFreqMatched(m.startDate, m.eventFreq, d.d)
ORDER BY
      m.eventFreq
    , d.d
;

=>

ID  startDate   weekday eventFreq   nextDate
1   2020-01-01  3   1   2020-02-19
1   2020-01-01  3   1   2020-02-26
1   2020-01-01  3   1   2020-03-04
1   2020-01-01  3   1   2020-03-11
2   2020-01-02  4   2   2020-02-27
2   2020-01-02  4   2   2020-03-12
3   2020-01-03  5   3   2020-03-06

Note 1: I used session variables to specify date range for simplicity. In real life you should probably calculate it based on your search criteria or from current date.

Note 2: I do not understand how you'd like to 'every other' would be calculated. From beginning of month of the date or from startDate record. I'm implemented the later one in the SP. If you need another, you have to adjust the function.