PHP / MYSQL比较日期

Question

I have a date in php formatted like this:

Fri May 01 2015 17:25:00 GMT +0100 (GMT Daylight Time)

And Im trying to compare it to a mysql DATETIME field:

$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 executes it returns 0 results even though based in the input and elements in the database it should pull results.

example of data in the datetimefeild feild:

2015-05-16 07:44:56

The date is being passed to php via ajax post from jQuery datepicker which is setup like this:

$("#datepicker").datepicker({ dateFormat: 'yy-mm-dd' });
var start = $.datepicker.parseDate("yy-mm-dd", $('#datepicker').val());

Answer 1

You are passing a the date in wrong format (as string). You should first convert it to datetime type in the where clause using str_to_date() function.

Example:

SELECT str_to_date('2015-05-01 08:00:00', '%Y-%m-%d %h:%i:%s')

Applying it, your $sql string would 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";