CODEWITHSUNDEEP
phpmysql
SirBT
SirBT

Reputation: 1698

Why isnt my MySQL BETWEEN operator not working?

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.

Upvotes: 0

Views: 620

Answers (4)

Toni Tegar Sahidi
Toni Tegar Sahidi

Reputation: 933

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.

Upvotes: 2

Nero
Nero

Reputation: 1285

Your time calculation with date("H:i",...) and strtotime(..) seems to actually produce the correct results, although there is a much easier way to add/substract n minutes from the current time:

$now = time();
$currentTimeMinus30min = $now - 30*60;    // 30 minutes * 60 seconds
$currentTimePlus4Hours = $now + 4*60*60;  // 4 hours * 60 minutes * 60 seconds

(I assume your time entries in your database are unix timestamps.)

Your query looks fine, too, but there are a few things to keep in mind:

  • You have redundant fields in your database (day and time can be calculated from the timestamp)
  • Working with time variables can easily lead to confusion, as the time passes on and if you have no entries in your database that match the specified time range (-30m to +240m) the result set is empty. So to test the query update the database with current time stamps.

I would suggest the following:

Drop the redundant columns day and time and just use the timestamp as base for your calculations, because the day and time is already included in the timestamp. So just use a simple query like

select * from flightShed
where arrivalTime between $begin and $end

Upvotes: 0

RJD22
RJD22

Reputation: 10340

Have you tried to encapsulate the between? This could potentially solve your problem: 

SELECT * FROM flightSched 
WHERE day = '$currentDay' 
AND (arrivalTimeSec BETWEEN '$CurrentTimeMinus30min' AND '$CurrentTimePlus4Hours')

Also why not just do:

$CurrentTimeMinus30min = strtotime('-30 minutes');

Or

$CurrentTimeMinus30min = strtotime(date('Y-m-d H:i:00', strtotime('-30 minutes')));

Please send us some examples of what your variables are generating.

Upvotes: 0

anothershrubery
anothershrubery

Reputation: 20993

strtotime returns a timestamp so passing that into the MySQL query, like above, won't work. Try using FROM_UNIXTIME instead.

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

EDIT - I hadn't noticed that arrivalTimeSec was also a timestamp. The above mightn't be a workable answer for you, but try it. If it doesn't work, as others say, define what you mean by

This doesn't seem to be working at all.

Is it not returning any rows? Is it returning an error? Can you print out $CurrentTimeMinus30min and $CurrentTimePlus4Hours? Narrow down the potential areas for problems.

Upvotes: 0

Related Questions