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

  • It sounds like you want to re-implement cron. Cron is built around an algorithm for efficiently waiting for a list of scheduled tasks with precision of 1 minute. Why not just put the task times in cron and let it launch them? Convert them to a common timezone (which could be UTC) first. – joshp May 7 '17 at 10:15
  • So the tasks are derived from a colum in the MySQL database, I am new to cron but I didn't think you could query databases's and execute commands based on that. Accuracy to a minute is indeed what I am looking for, so using the Timer aspect of java and run it every minute or manually setting a delay based on the time difference between two execution times would indeed work? – 97WaterPolo May 7 '17 at 19:38
  • Sure a Java based timer will work too, and should be simple for a small enough workload with enough time to evaluate upcoming tasks before missing a task deadline. There are also Java based implementations of cron-like scheduling such as the Spring TaskExecutor or the Quartz Scheduler. They can be complicated, but they have already done some implementation work and testing. If you use cron you can query to make a crontab instead of writing the scheduling. One thing cron doesn't do in my experience is track whether a task is done. That can be important if you ever have to restart. – joshp May 7 '17 at 22:08

You don't specify a language but most provide a timer class which allows you to run some code after a delay.

Check the next earliest time on startup and then wait.

Of course you'll have to handle the edge cases, new task comes in before the one you are waiting for, program exits while waiting etc.

In regards to time zones, you should store all date times as UTC

  • I figured I could go the Thread route and just .sleep until the next time it runs. If I sorted the MySQL database to go in order based on UTC time, could I start with the first user and just sleep until the next time comes up? – 97WaterPolo May 7 '17 at 8:54
  • I would advise against programming your own timer as there are many 'gotchas'. use the one provided by your language of choice – Ewan May 7 '17 at 8:56
  • Sorry I updated the main post to reflect the language, Java. Would sleeping a thread the specified time between each user's task execution time be an acceptable approach to this? – 97WaterPolo May 7 '17 at 8:57
  • Alright so if I use a timer, do I have it run every minute? Every 5 minutes? Even with the timer how would I load in the MySQL data for all the users. Would I still sort them according to time based on UTC and every minute VIA the timer grab all the rows that correspond to that time? – 97WaterPolo May 7 '17 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.