PHP / MYSQL comparison time
I have a date in php formatted like this:
Fri May 01 2015 17:25:00 GMT +0100 (GMT Daylight Time)
And I am trying to compare it to the DATETIME field of mysql:
$date1 = DateTime::createFromFormat("Y-m-d H:i:s e+", $start);
$sql = "SELECT * FROM ax_timestamps WHERE $date1 < datetimefeild ORDER BY id ASC";
But when it does this, it returns 0 results, even if they are based on the input, and the items in the database should be fetching results.
example data in datetimefeild feild:
2015-05-16 07:44:56
The date is passed to php via ajax message from jQuery datepicker, which is configured like this:
$("#datepicker").datepicker({ dateFormat: 'yy-mm-dd' });
var start = $.datepicker.parseDate("yy-mm-dd", $('#datepicker').val());
+3
source to share
2 answers
You are passing the date in the wrong format (as a string). You must first convert it to datetime type in the where clause using the str_to_date () function.
Example:
SELECT str_to_date('2015-05-01 08:00:00', '%Y-%m-%d %h:%i:%s')
By applying it, your $ sql string will become:
$sql = "SELECT * FROM ax_timestamps WHERE datetimefeild < str_to_date('".$date1."', '%Y-%m-%d %h:%i:%s') ORDER BY id ASC";
EDIT: This also works:
$sql = "SELECT * FROM ax_timestamps WHERE datetimefeild < '".$date1."' ORDER BY id ASC";
0
source to share
I think that
Fri May 01 2015 17:25:00 GMT +0100 (GMT Daylight Time)
is not a valid date / time format. You should only have
Fri May 01 2015 17:25:00 GMT +0100
so you can do it
$date1 = new DateTime(substr($start, 0, -20));
and
$sql = "SELECT * FROM ax_timestamps WHERE {$date1->format('Y-m-d H:i:s')} < datetimefeild ORDER BY id ASC";
0
source to share