mysql select between two dates has odd behavior

Question

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

Strange. All I get is a syntax error. Can we see your actual queries. :-( — Strawberry
– Strawberry, Commented May 30, 2018 at 6:02

Tim Biegeleisen · Accepted Answer · 2018-05-30 06:07:47Z

3

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.

edited May 30, 2018 at 6:07

answered May 30, 2018 at 2:03

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Strawberry Over a year ago

Is everyone using a different version of MySQL to me? The queries are equivalent in the sense that they both produce the same syntax error. !?!

Tim Biegeleisen Over a year ago

@Strawberry Thanks for the correction. I guess we can't assume that anything in any OP is actually working a priori. Maybe the OP made a typo when copying over to Stack Overflow.

rolinger Over a year ago

Thaks @TimBiegeleisen ...i understand now. I added your explanation in code to the OP. I didn't realize the BETWEEN literally translated to date >= value AND date<= value

Jacky · Accepted Answer · 2018-05-30 02:10:12Z

0

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 :)

answered May 30, 2018 at 2:10

Jacky

3,2894 gold badges25 silver badges37 bronze badges

Collectives™ on Stack Overflow

mysql select between two dates has odd behavior

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related