为什么我的MySQL BETWEEN运算符不能正常工作？

Question

MySQL table "flightSched" is connected to time, similar to the one below:

flightNo    |day    |time   |arrivalTimeSec
=============================================
WERE112 |Tuesday    | 1:00  |1381186800
FGHG234 |Tuesday    |23:00  |1381266000
CGHF345 |Tuesday    |00:00  |1381183200

I have a mysql query that select all data between two times. This is the query:

$CurrentTimeMinus30min = date('H:i', strtotime('-30 minutes')); //Current Time minus 30minutes
$CurrentTimeMinus30min = strtotime($CurrentTimeMinus30min);     

$CurrentTimePlus4Hours = date('H:i', strtotime('+240 minutes')); //Current Time plus 4 hours
$CurrentTimePlus4Hours = strtotime($CurrentTimePlus4Hours);

$query = $mysqli->query("
    SELECT * FROM flightSched 
    WHERE day = '$currentDay' 
    AND arrivalTimeSec 
        BETWEEN '$CurrentTimeMinus30min' 
        AND '$CurrentTimePlus4Hours'
");

I was advised to used strtotime() function on the time values to be able to use them in a BETWEEN MySQL query. This doesn't seem to be working at all.

Where am I going wrong with this query? Any help will be appreciated.

Answer 1

today I found the same problem with yours (mine about coordinates). and I found out that in some case, a BETWEEN operator can only be used like this

..... WHERE columname BETWEEN smallervalue AND biggervalue

previously I've tried with the biggervalue at front since I dealt with negative numbers, and it fails. you might found the same problem with your timestamp.