CODEWITHSUNDEEP
mysqlsqldatetimebetween
rolinger
rolinger

Reputation: 3058

mysql select between two dates has odd behavior

I am selecting all records between NOW() and specific X day interval and came across this odd behavior that I don't understand.

I am checking 24 hours into the future and 24 hours into the past:

select * from table where date between NOW() and NOW() + 1 interval day; //works
select * from table where date between NOW() and NOW() - 1 interval day; //no records

But if I reverse the between call:

select * from table where date between NOW() + 1 interval day AND NOW(); //no records
select * from table where date between NOW() - 1 interval day AND NOW(); //works

Why does one call into the future work, but the same call into the past not work?...and if I reverse between parameters, the opposite behavior happens - does not work 24 hours into the future but does work 24 hours into the past.

======================

Adding @TimBiegeleisen explanation below here written out:

date = '2018-05-30' ;

select * from table where date between NOW() and NOW() + 1 interval day;
     = date >= '2018-05-30' AND 'date <= 2018-05-31'; //true
select * from table where date between NOW() and NOW() - 1 interval day; records
     = date >= '2018-05-30' AND 'date <= 2018-05-29'; //false

AND

select * from table where date between NOW() + 1 interval day AND NOW();
     = date >= '2018-05-31' AND date <= '2018-05-30' //false
select * from table where date between NOW() - 1 interval day AND NOW();
     = date >= '2018-05-29' and date <= '2018-05-30'; //true

Upvotes: 0

Views: 291

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521279

The BETWEEN operator is interpreted a certain way:

WHERE date BETWEEN a AND b

means this:

WHERE date >= a AND date <= b

So the following two queries are equivalent:

select * from table where date between NOW() and NOW() - interval 1 day;
select * from table where date >= NOW() and date <= NOW() - interval 1 day;

Hopefully you can see that in your second query the WHERE condition can never be true, because a date cannot simutaneously be greater than or equal to now and less than now minus one at the same time.

Upvotes: 3

Jacky
Jacky

Reputation: 3239

simply put,

For SQL: WHERE x between a and b meaning

x >= a and x <= b

therefore, we have a <= x <= b or a <= b

PS: it's just about math :)

Upvotes: 0

Related Questions