2

I'm attempting to to get all conflicting time schedules from an SQL database similar to this:

SELECT * FROM schedule WHERE x BETWEEN startTime AND endTime

Where x is any time given in a timestamp.

The issue lies with the fact that if I have say a schedule from 1:00 PM to 2:00 PM and then I attempt to use x = 2:00 PM and startTime = 2:00 PM I get a return from my statement execution.

Is there a flag or something similar that allows a non-inclusive BETWEEN call?

Joe
  • 269
  • 3
  • 13

1 Answers1

2

BETWEEN..AND.. is basically syntactic sugar for combination of >= AND <= operators, with same performance.

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)

For non-inclusive range checking, you can use > AND < operators:

SELECT * FROM schedule 
WHERE x > startTime AND 
      x < endTime
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • 1
    I was specifically told not to use those operators on timestamp values by the community in another question, are you sure this is the proper way? – Joe Dec 02 '18 at 09:44
  • It's a simplified version of what I'm doing that I wrote on the fly and didn't catch that I had misplaced them, I'll edit that in the original post. – Joe Dec 02 '18 at 09:45
  • @Joe where did you read that? I will be happy to know the link and rebut there. Because that would be sheer nonsense. Don't use functions on the columns, because that avoids usage of indexing. However, there is no such restriction on operators. Infact `BETWEEN` operator is just a syntactic sugar for `>= .. AND <=` – Madhur Bhaiya Dec 02 '18 at 09:45
  • Alright, I appreciate the information. If I find where I was told to use them I will make sure to link it but for now I'll select your answer when I'm able to. – Joe Dec 02 '18 at 09:46
  • Two things 1) There are claims that BETWEEN should not be used when dealing with *dates* as it might lead to unexpected results (see the first comment on the most voted comment here https://stackoverflow.com/questions/1630239/sql-between-vs-and). 2) There are claims that AND somehow uses indexes better than BETWEEN which seem dubious: https://www.datacamp.com/community/tutorials/sql-tutorial-query – Heberto Mayorquin Nov 18 '21 at 14:22