CODEWITHSUNDEEP
phpmysqldatetime
Ali
Ali

Reputation: 267317

MySQL date/time calculation

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?

Upvotes: 1

Views: 2961

Answers (4)

Paul Tomblin
Paul Tomblin

Reputation: 182878

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.

Upvotes: 2

Ali
Ali

Reputation: 267317

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
}

Upvotes: 1

Sampson
Sampson

Reputation: 268502

Something like this:

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

Upvotes: 4

&#211;lafur Waage
&#211;lafur Waage

Reputation: 70031

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.

Upvotes: 0

Related Questions