I have a question in regards to the implementation of a program that I am trying to do. I have a MySQL database with numerous users, and each have a task executed at a certain time.
For each user they have a time for the task to be executed, and a time zone for the task to be executed (each seperate colums in the MySQL db). So far when I've been testing things I excecute the program every 5 minutes VIA cron, it loops through all the users and checks to see if (timeToBeSent)-(currentTime) < 5, if it is the task is executed. I know this isn't 100% accurate but it was the closest and easiest way to start some testing.
Now that I have finished the general idea of my program, what are some options for this to work?
- I could keep looping through all the users every 5 minutes but that is going to be resource intensive, and not accurate to the minute.
- Another idea where I convert all the times the task is executed to a common time zone, then sort the database based on the time. Once I've done this just run it every minute and select the rows that pertain directly to that current minute
- EDIT: Another idea from the comments would be to sort the db in order based on all the times converted to UTC. Start with the first user at the earliest time and execute the task. Then I would find the time difference between that user and the next and have the Thread sleep until the next user's time comes up.
I'm rather new to software engineering, and I haven't taken school classes on design implementation at my school so if there is a way to do this I would love if links to articles/sources could be posted so that I can learn.
Thank you very much!
EDIT: Language is Java