I am facing one problem for fetching records from table. My table look likes below:-
id market_id opening_time closing_time day p_slot
1 1 06:30:00 09:30:00 Friday 1
2 1 09:30:00 13:30:00 Friday 0
3 1 17:30:00 19:30:00 Friday 0
4 1 19:30:00 23:30:00 Friday 0
I want to filter data based on opening_time and closing time. I have tried below query
select *
from `timing`
where market_id = 1
and day = 'Friday'
and (opening_time <= '13:45:00' or closing_time <= '15:30:00')
and (opening_time <= '15:30:00' or closing_time <= '15:30:00')
But when i run this query i got below response that is wrong
id market_id opening_time closing_time day p_slot
1 1 06:30 09:30 Friday 1
2 1 09:30 13:30 Friday 0
But yes if user search for below query then above output is correct
select *
from `timing`
where market_id = 1
and day = 'Friday'
and (opening_time <= '09:15:00' or closing_time <= '12:30:00')
and (opening_time <= '12:30:00' or closing_time <= '12:30:00')
Can anyone help me to resolve the issue? I want result that exists in opening_time and closing_time range.
Note :- i want when user search with from time :- 9:15:00 and end time 12:30:00 i want to fetch 1st and 2nd rows only and if user search with 13:45:00 and 15:30:00 on that case i dont want any row to filter
Your condition should be:
closing_time >= from_time and opening_time <= end_time:
Like:
select *
from `timing`
where market_id = 1
and day = 'Friday'
and closing_time >= '13:45:00' and opening_time <= '15:30:00';
See the demo.