MySQL，在格式化为字符串的两个日期之间获取数据

Question

Would be grateful for some help!

Database Tables are set up like this:

id(varchar),
temp(varchar),   
humi(varchar),   
time(varchar)

Then I thought the user to input the ID, start date and end date. The problem is how the string in the Time column is formatted, example: 18/03/14: 21:52:36

The user should not have to enter the time, just the date.

I thought it would be possible to do in a similar way:

$result = mysql_query("SELECT * FROM $tbl WHERE id = '$id' AND time BETWEEN '$start%' AND '$stop%'");

But it did not work.

Is it possible to do this with a sql query when the date is stored in such a way?

Regards

. Anders

Edit:

It did not work, probably because I'm doing wrong though = /

If I do this:

    $start= "13/02/14 : 12:17:34";
    $stop = "13/02/14 : 12:36:18";
    $result = mysql_query("SELECT * FROM $tbl WHERE id = '$id' AND tid BETWEEN '$start' AND '$stop'");

..the data will appear as expected

But when I try to to use str_to_date () ,it did not work as I thought, or it did not come out any data at all.

    $start= "13/02/14";
    $stop = "10/02/14";
    $id = "3E000004C6DB8D28";
    $result = mysql_query("SELECT * FROM $tbl WHERE id = '$id' AND tid BETWEEN str_to_date('$start%', '%d/%m/%Y') AND str_to_date('$stop%', '%d/%m/%Y')");

edit2:

Do not really know what I was doing weird the first time, but now it works with this code:

$result = mysql_query("SELECT * FROM $tbl WHERE id = '$id' AND tid BETWEEN '$start' AND '$stop'");

Answer 1

You need to use str_to_date():

WHERE id = '$id' AND
      tid BETWEEN str_to_date('$start%', '%d/%m/%Y') AND str_to_date('$stop%', '%d/%m/%Y')

Obviously, you can also do this in the application before inserting the values into the query. If so, convert the values to 'YYYY-MM-DD' format.