Where is difference between using of mySQL operator BETWEEN and classic date interval? [duplicate]

Question

Possible Duplicate:
Difference in SQL Between operator and “>=” & “<=” operator

I see that my developer is using this construction for selecting data of selected date interval:

SELECT ... WHERE `date` >= '2011-11-28' AND `date` <= '2011-12-04'

I recommend him to use BETWEEN operator like this one:

SELECT ... WHERE date BETWEEN '2011-11-28' AND '2011-12-04'

Which solution is better and what are arguments for it?

Answer 1

AFAIK, there is no difference between this approaches in terms of execution efficiency. But BETWEEN X AND Y seems to be more readable and nice, so I'd like to use this construction instead of date >= '2011-11-28' AND date <= '2011-12-04' too.

BUT: If for example you are write simple datetime filter in your app, you need an opportunity to pick only first or only second date - this is the case where date >= '2011-11-28' AND date <= '2011-12-04' should be used (we can omit date >= '2011-11-28' or omit date <= '2011-12-04' in our query builder without problems).

Answer 2

I'm not sure that is the 'clasic' date interval e.g. for the period of the current month I would have though it was

SELECT ... WHERE `date` >= '2011-12-01' AND `date` < '2012-01-01'

The idea is that time is a continuum and a period is infinitely divisible. Put another way, is not in theory possible to define the last time granule of the current month. Is it one microsecond before midnight of January 1st? One nanosecond?

Well, in practice it may be possible to define a minimum time granule using a temporal data type in the DBMS of your choice. However, this changes the model of time from a continuum to a series of discrete points in time and you would need to do a lot of extra work to ensure comparisons are done using the same time granules. The classic bug is where the current month is defined as start_date = 2011-01-12 end_date = 2011-12-31 at time granule of one day then someone wonders why the DATETIME value 2011-12-31 12:00:00 does not fall in the current month or, worse, falls in no month at all!

In contrast it is easy to define the first time granule that is not included in the current month i.e. January 1st at one day time granule. This is referred in the literature as closed-open representation or "half-open" and seems to be the most widely employed.

In conclusion, unless you can ensure that temporal values of less than one day time granule can be effectively eliminated from your model (and I suggest that you cannot!) then use closed-open representation and avoid BETWEEN to test whether a values falls in a period.

Answer 3

From MySQL Manual:

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type.