MySQL date/time calculation

Question

I have a date and time column in my mysql table called start_date and start_time respectively. I want my users the ability to set reminders for themselves to be sent X hours before start_time, min. 1 hour and max 24 hours.

I'll be running a CRON script to send these reminders. Right now I do:

SELECT * FROM auctions WHERE start_date=CURDATE() AND status='0'

To get all the auctions that will be starting today and haven't yet started. My question is, how can I figure out if the time now is X hours before start_time so I can send them a reminder if it is.

Any suggestions at all?

Answer 1

Something like this:

SELECT col1, col2, col3 
FROM records 
WHERE (records.startDate BETWEEN NOW() AND ADDDATE(NOW(), INTERVAL 9 HOUR)) 
AND (records.status = '0');

Answer 2

Is there some reason why you can't just use a simple timestamp field instead of one for date and one for time. That way you could find all the ones that start in the next 5 hours (say), by doing

   select * from auctions where start_ts between now() and now() + interval '5 hours';

Note: the interval syntax varies slightly between databases, and that's the one for postgresql, so you might have to change it slightly for mysql.

Answer 3

Can you use unixtime to save the time?

Since PHP has a wonderful function called strtotime.

Within in you can say. strtotime("+20 hours") and get the unixtime for 20 hours from now.

Then its just a matter of which field is larger than the other, if so, send the notification.

Answer 4

I actually did it this way before all the answers were sent and its working. Because i'm on a deadline I can't go back and change it :)

   $sql="SELECT HOUR(ADDTIME(CURTIME(),'$hour')) as remindHour,
     HOUR(CURTIME()) as curHour";
   $result=$this->db->query($sql);
   extract($result->getAllSingle());

   if ($remindHour <=$curHour)
   {
    //Send reminders   
   }