How to make the correct organization of "reminders" at the database level?

Question

PostgreSQL

Good afternoon! Guys who made reminders (scheduled tasks) in their projects!? share your experience on how best to organize everything, store it in a database, so that later it would be convenient and quick to make a selection of records.
For example, there is a TASK table, and it needs to be executed according to a specific schedule. Accordingly, we need, for example, a selection of tasks that will be completed today or, for example, a week in general, depending on how the user indicated.
Options that immediately came to mind:
1) Add a string field in the cron format, for example "0 0 * * *" - (every day at midnight)
It’s very convenient, but how to make a selection later, you need to parse this line first using the database, then make a query based on the result, but most importantly, when the table grows, parsing will have a bad effect on the selection speed.
2) The same, but only we put each parameter in a separate field, and as I understand it, if you need to specify several months or several days, then we store them as a bit mask. The advantage is that we get rid of parsing, but we get a new problem - mask selection, how fast and convenient will it work?
3) We make a full-fledged table structure, for months, days, hours our table with links.
4) We give everything to the application server, keep a separate cache for these purposes and synchronize it with the database, in principle, you can use the cron format as in the first option
I don't like any of the methods. Advise how it is better to make those who already faced similar tasks. Maybe there is a more beautiful and correct way.
My database is postgresql.

Answer 1

This is how I implemented it. We store all dates in a timestamp format, which is easier to store and calculate. Exceptions are stored in an arbitrary form key / value, I only have clocks implemented with keys from to. Tasks come in several states: active, inactive, overdue, future, endless.
The task itself looks something like this (this is exactly the entry in the table):
- start - start date (timestamp)
- delta - real start offset from the start start ( this is a feature of my application, nafig is not needed, just so as not to spoil the code )
- period - repetition period (timestamp as timedelta)
- duration (should be less than or equal to periodtimestamp as timedelta)
- stop - end date (may be absent, timestamp in the form of timedelta)
- count - number of repetitions from the start date (if any, then the end date must be set, int )
- exceptions (for example, without Mondays or only even hours, or time from 10 to 19)
- active (yes/no)
It works like this.
- Select all active tasks where the start is less than the desired date and the stop is zero or greater than the desired date.
- We go over all the tasks and for each we calculate the hit on our date.
- If there are exceptions, we look to see if we fall into them.
- If there is a count, calculate it
- If there are exceptions along with count, then we recalculate count for each period - the most resource-intensive process, you have to run through all the intervals from the start.
Base class (in python):

# функция преобразования питоновского класса datetime в timestamp
# возвращает timestamp (int)
def total_seconds(td):
    seconds = (td.microseconds + (td.seconds + td.days * 24 * 3600) * 10**6) / 10.**6
    ret = 0 if seconds < 0 else seconds
    return ret

class interval(object):
# здесь должно быть все понятно
# self  - указатель на сам класс, особенность питона, на другом языке этот параметр опускаем
# вместо self на других языках используем this или аналог
    def __init__(self, start, delta, duration, period):
        self.start = start
        self.delta = delta
        self.duration = duration
        self.period = period

# вычисление последнего интервала от нужной даты
# возвращает две даты от и до
    def last(self, date):
        _start = self.start+self.delta 
        mul = int(total_seconds(date - _start)/total_seconds(self.period))
        at = _start+(self.period*mul)
        to = at+self.duration
        return (at,to)

# является ли интервал активный на нужную дату
# возвращает true/false
    def isLive(self, date):
        at, to = self.last(date)
        f1 = date >= at
        f2 = date < to
        return f1 and f2

# вычисление следующего интервала от нужной даты
# возвращает две даты от и до
    def next(self, date):
        at, to = self.last(date)
        return (at + self.period, to + self.period)

The rest, I will not cite, as there is a lot and I hope you will add it yourself.

Answer 2

How would I implement it:
In an arbitrary form convenient for you, we store the periodicity (you can, as you wrote in the first version, in the krone format). Additionally, we store the date of the next occurrence of the event in a separate field. Accordingly, we conduct all samples simply by date.
This is for when the task does not have an execution status, no need to look at past/future tasks, etc.
If you want to do it like in google calendar / icloud - where each instance of this recurring task is essentially independent enough - it gets a little more complicated.
It turns out 2 tables: a prototype task where the frequency, the date of the first run and optionally the date of the last one are stored, and an instance task where a specific date and a link to the prototype are stored.
It turned out chaotically, I haven’t woken up yet :)
If so, let's discuss :)