How to organize the logic of sending emails on a schedule?

Question

MySQL

Hello.
There is a table with the following structure and records that look like this:
id 1
user_id 5
start_time 08:00:00
finish_time 18:00:00 interval
10 The
interval is the number of minutes after which the task must be completed. In my case, this is sending a letter, every 10 minutes during business hours, from 8 am to 6 pm.
Each user can theoretically have a different time and a different interval.
I ran into difficulty. A script that will run every minute. Must somehow select all users who have sending at the moment. That is, in our example. The script should work for our user at 8-00, at 8-10 and so on.
Who can faced the given task? And can you advise?
I already thought that storing intervals in this form might complicate the task. And it's better to store it with the same values:
8:00
8:10
...

Answer 1

Do you have all the data for this, are you completely lazy to think?))
Knowing that your script runs every minute, and in it:

1. Get the current time ( nowTime )
2. Check nowTime <= finish_time , if so, continue, otherwise exit the script
3. Calculate the difference between nowTime and start_time in minutes - diff
4. If diff % interval == 0 , then send the message, otherwise not.

Answer 2

You can't write a query for the occurrence of one time interval in another?
If so, then google to the rescue: sql date entry into the interval
Or you can not implement a script that runs every minute?
If so, google to the rescue: cron

Answer 3

Use cron. The scheduling logic in it allows you to set periods, frequencies and times of any complexity.

Answer 4

Just add a field indicating the next send time, say `next_time` TIME.
Selecting records to send:

SELECT `id` 
    FROM `table` 
    WHERE CURTIME() BETWEEN `start_time` AND `finish_time` 
        AND `next_time` < $current_time;

Receiving a list of tasks, we simultaneously form it for the request:

UPDATE `table` SET `next_time` = `next_time` + `interval`*60 
    WHERE `id` IN ({$task_list});
UPDATE `table` SET `next_time` = `start_time` 
    WHERE `next_time` > `finish_time`;