June 11th, 2016, 09:14 AM
Selecting records based on a future datetime
I am attempting to create a simple scheduling project and have a MySQL table with a
datetime field (I can change to timestamp if necessary).
In any case that specific field called starttime contains a future date and time (appointment) say 20 days
in the future.
I need to create a script that I can run as a cron job that will select all records that are between 15 minutes
to 20 minutes in the future from the current date and time so I can send an alert message of the upcoming
appointment is in 15 minutes.
I am not successful in the attempts I have made: here is the latest attempt at retrieving records:
$r1 = date("Y-m-d H:i:s", strtotime('+15 minutes'));
$r2 = date("Y-m-d H:i:s", strtotime('+15 minutes'));
$sql = "SELECT * FROM wp_appointments WHERE starttime >= $r1 AND starttime < $r2";
and I also tried:
$sql = "SELECT * FROM wp_appointments WHERE starttime >= DATE_ADD(NOW(), INTERVAL 15 MINUTE) AND starttime < DATE_ADD(NOW(), INTERVAL 20 MINUTE)";
I would appreciate any help in getting this sql query working correctly.
June 11th, 2016, 09:26 AM
Dates in MySQL work like strings Dates in MySQL queries are written with quotes just like how strings are, so with the first form you tried you would need to put quotes around the date values.
(and your $r2 does +15 minutes when it should be +20)
$sql = "SELECT * FROM wp_appointments WHERE starttime >= '$r1' AND starttime < '$r2'";
The second form should have worked. Did it fail? What was the error message? Or did it work but not find any results?
Last edited by requinix; June 11th, 2016 at 09:56 AM.
Reason: I had sacrificed accuracy for conciseness >_>
June 11th, 2016, 09:40 AM
not at all...
Originally Posted by requinix
... unless you are talking about how to specify a date value as a literal constant -- yes, that's done with strings
but dates in MySQL work like ~dates~ for cryin out loud, and deserve to be treated with date functions, not string functions
Comments on this post
June 11th, 2016, 09:52 AM
Yeah, okay, poor choice of words there I was thinking just about how to write the values.
June 11th, 2016, 10:08 AM
sorry if i was harsh, just one of my pet peeves