Notification cron

Question

I've built an appointment manager and would like to notify clients 2 hours ahead of time by via email of their scheduled appointment time. I am storing their scheduled time in a MySQL Datetime field.

Example:

2015年11月24日, 10:59:05

I currently have a cron scheduled to run every minute and collect the emails to be sent:

public function get_appointments_to_remind()
{
 //set time to two (2) hours from now
 $notify_time = date('Y-m-d, H:i:s', strtotime("+ 2 hours"));
 $this->db->select('email');
 //For those unfamiliar with codeigniter, produces WHERE column = "value"
 $this->db->where("start_datetime", "$two_hour_time");
 $query = $this->db->get($this->_appointments_table);
 return $query->result_array();
}

Is there a better way to go about this? I'm worried about potentially missing some emails because the times may not be exact when the cron runs.

• hjpotter92: DATETIME field does not have ,.

Answer 1

Yes, you will miss every appointment that doesn't have :00 for seconds. And if your cron errs for any reason or skips a second, you'll miss even more. You should add another DATETIME column to your table called notified_at (or something like that) and then do two conditions in your where clause:

1. WHERE start_datetime <= '$two_hour_time'
2. AND notified_at IS NULL

That will capture any appointments that fall within the 2 hour time and haven't been notified yet. Then update the notified_at column to the current date once the email has been sent.

Answer 2

Something you'll see sooner or later in production, although probably not in development, is duplicate emails.

Even a very slight hiccup due to higher load on your end or poor performance of the email service will cause the next minute's task to run before the first is finished.

I can say from experience when users see duplicates like this, even if they don't cause practical issues, they doubt the reliability of your service.

There are three ways to tackle this:

1. Create another DATETIME column on the table like locked_for_notification, get the current time at the beginning of the job, set locked_for_notification when start_datetime is in range, notified_at is null, and locked_for_notification is null, and then select for records with the correct start_datetime and the locked_for_notification time corresponding to the current job.

2. Create a lock file like (for instance) /tmp/email-notification.lock and check for its existence at the beginning of the job. If it doesn't exist, create it and proceed with notifications, when finished set notified_at and exit. If it does exist, just exit, possibly notifying you by email that there may be a potential issue.

3. Check the running processes at the beginning of the job to see if another instance of the job is running. If not, proceed and if so exit, and possibly alerting you in some way that all is not well.

Let me know if I can clarify any of this!