How to store the interval schedule in the database?

Question

MySQL

Hello.
The question is rather complex. What is the best way to store a schedule of any recurring event in a relational database (for example, on some days of the week or month, etc.) with the ability to remove the event at a specific period or point in time.
These can be payments, reminders, or let's take a training schedule as an example.
There is a coach, he created a training schedule, for example, on Tuesday, Thursday and Saturday. And here is this schedule "scaled" on his calendar. But on New Year's holidays, the coach does not work, and from 01.01 to 10.01 the schedule should be empty.
Please share your experience or links.

Answer 1

An easy way is Linux kroner, in which, using a fairly simple grammar, you can set intervals in fairly broad rules. Everything is set there like this:
minute hour day_week day_month month command
Each of the numbers is either (*), or the number (5) or several numbers (2,3,5, 7-10), or a gap (something / interval).
Therefore, you can easily set events like every second Tuesday
* * 2/2 * * command
or the first day of May
0 0 * 1 4 command 2
Based on this, regular events can be built. To impose a schedule of holidays on them (and even then not always, for example, payment of household bills does not depend on holidays). Alternatively, you can move the event if it falls on a holiday.

Answer 2

One table for exception days, i.e. holidays. Holiday start date and end date.
The second is for the events themselves, which are:
- One-time - a specific day.
- Repetitive. This is where the settings are needed.
* Start day of the period when events are active (you can separate the field with a specific one-time day)
* Day of the end of the period or null for unlimited
* Repeat type:
1. Specific day of the week (number from 1 to 7)
2. After a certain number of days (number)
- Workout
ID - Group ID (in order to do complex variations of the type you specified "Tuesday, Thursday and Saturday")

Answer 3

1. First of all, the schedule can be set in a natural language, or stored in JSON by inventing some kind of DSL, regardless of the database. An excellent library for this: https://github.com/kvh/recurrent (although it only supports English). If you need, for example, to do mailings, you run through all active schedules in a loop, check if the current date matches the schedule, and perform the action.
2. If you want to make such a selection very quickly and using database tools, then when entering the above high-level schedule, you can fill in all the dates in a certain cache (or in a separate table in the database), setting a flag for the pair (date, schedule id). When the schedule changes, this matrix is ​​recalculated for it.
3. What about excluded days? You can offer two fields in each of the objects: "Schedule" (for example: every friday) and "Excluding" (for example: 1 Jan - 10 Jan).

Answer 4

Bit string one bit - one interval.