SQL Select Statement with where, AND, OR not working correctly

Question

Hello everyone i have the following row as a sample in my table

id      shop_id     start_time      end_time
1       21          10:00           11:00

and i want to check whether start_time and end_time exist in table or not I am using following query but not working correctly, Where i am wrong ?

select * 
  from usr_booking 
 where shop_id='21' 
   and start_time between '10:10:00' and '11:01:00' 
    or end_time   between '10:10:00' and '11:01:00'

The syntax in your query has a problem, yet despite that the single record you showed us in fact should be matched. What is the exact problem? — Tim Biegeleisen, Jan 19 '19 at 06:36
Try using >, < operators. See https://stackoverflow.com/questions/17407481/check-if-a-time-is-between-two-times-time-datatype — danv, Jan 19 '19 at 06:44
Are `start_time` and `end_time` actually date types? Or is the column another type? — entpnerd, Jan 19 '19 at 06:45

score 2 · Accepted Answer · answered Jan 19 '19 at 06:40

You need to clearly separate the checks on the shop_id and the time ranges:

SELECT *
FROM usr_booking
WHERE
    shop_id = 21 AND
    (start_time BETWEEN '10:10:00' AND '11:01:00' OR
     end_time BETWEEN '10:10:00' AND '11:01:00');

The AND operator in MySQL has higher precedence than the OR operator. So, your current query is actually evaluating as this:

SELECT *
FROM usr_booking
WHERE
    (shop_id = 21 AND
     start_time BETWEEN '10:10:00' AND '11:01:00') OR
     end_time BETWEEN '10:10:00' AND '11:01:00';

Clearly, this is not the same logic as you what you probably intended.

this wont work if start time is 14:00 and end time is 09:00 (i.e next day) any solution for this? — paradox, May 28 '20 at 21:05

score 0 · Answer 2 · answered Jan 19 '19 at 06:36

0

Try to make groups in such type of queries

select * from usr_booking where shop_id='21' AND ((start_time between '10:10:00' and '11:01:00') OR (end_time between '10:10:00' and '11:01:00'))

Add the extra brackets make groups.

answered Jan 19 '19 at 06:36

Sanjit Bhardwaj

893
7
13

score 0 · Answer 3 · answered Jan 19 '19 at 06:39

0

Try this Query:

SELECT *
FROM usr_booking 
Where shop_id='21' AND start_time BETWEEN '10:10:00' AND '11:01:00' 
AND end_time BETWEEN '10:10:00' AND '11:01:00'

answered Jan 19 '19 at 06:39

Nimesh Patel

796
1
7
23

score 0 · Answer 4 · answered Jan 19 '19 at 06:43

0

Try this

SELECT * FROM usr_booking WHERE shop_id=21 AND (start_time BETWEEN '10:10:00' AND '11:01:00') OR (end_time BETWEEN '10:10:00' AND '11:01:00')

answered Jan 19 '19 at 06:43

Ram

84
7

score 0 · Answer 5 · answered Jan 19 '19 at 11:17

If you are given the times '10:10:00' and '11:01:00' and you want to know if anything overlaps with that period, then the logic is:

select b.* 
from usr_booking 
where shop_id = 21 and -- do not use single quotes for numeric constants (assuming `shop_id` is a number
      end_time > '10:10:00' and
      start_time < '11:01:00';

For overlapping intervals, between is not appropriate.

SQL Select Statement with where, AND, OR not working correctly

5 Answers5

Linked

Related