SQL BETWEEN expression ... Inclusive Inclusive. Perhaps add rangeExclusive() rangeInclusive() ?

[Rob Bygrave]

Hi,

So I'm doing something (ElasticSearch expressions) and had a "Hmmm moment with BETWEEN which in SQL is Inclusive at both ends.

Reference for interest:

http://programmers.stackexchange.com/questions/160191/why-is-sqls-between-inclusive-rather-than-half-open

That prompts me to think that:

A) Should between() be renamed to betweenInclusive()?  

That is, I wonder if people are currently using BETWEEN and inadvertently thinking it is exclusive of the upper value and actually have a subtle bug? Maybe between() should be renamed?

B) Perhaps no one uses between() much because it is vague to read and being inclusive on the upper value is not so useful for date/date time/real number ranges?

C)  Especially in regards to Date and DateTime ranges I think it would be most useful to have a betweenExclusive(lowerValue, upperValue) expression ... where the upperValue is exclusive. 

Generally there are 4 Range modes but I think 1) is the most useful?

     1. Inclusive Exclusive  ... IMO this is more useful and we don't have a nice way to do this

     2. Inclusive Inclusive  ... this is SQL BETWEEN,  our existing between() expression

     3. Exclusive Inclusive

     4. Exclusive Exclusive

Part of me thinks that I should add 2 range expressions: 

rangeExclusive(a, b) ...    where property >= a and property < b

rangeInclusive(a, b) ...    where property >= a and property <= b

  ... so this is the same as SQL BETWEEN

... and deprecate between()  ... or rename it to betweenInclusive() to be more explicit

Any thoughts on this?

Cheers, Rob.