CODEWITHSUNDEEP
mysql
user1624540
user1624540

Reputation: 91

Check if a time is between multiple times (time DataType)

I'm working on Booking module with time scheduling. for that i have two different time_data one for time and other one booked_time. Now i want to show the available.

here is the time_data table with data

|---|---------|
|id |vtime    |
|---|---------|
|1  |08:00:00 |
|2  |09:00:00 |
|3  |10:00:00 |
|4  |11:00:00 |
|5  |12:00:00 |
|6  |13:00:00 |
|7  |14:00:00 |
|8  |15:00:00 |
|9  |16:00:00 |
|10 |17:00:00 |
|11 |18:00:00 |
|12 |19:00:00 |
|13 |20:00:00 |
|14 |21:00:00 |
|15 |22:00:00 |
|---|---------|

here is the booked_time table with data

|----|----------|----------------|------------|----------------|-----------|---------|
| id | userid   | facilityid     | courtno    | bookingdate    | starttime | endtime |
|----|----------|----------------|------------|----------------|-----------|---------|
| 1  | 1        |  1             | 1          | 2017-02-20     |08:00:00   |11:00:00 |
| 2  | 3        |  1             | 1          | 2017-02-20     |13:00:00   |15:00:00 |
| 3  | 2        |  1             | 1          | 2017-02-20     |11:00:00   |13:00:00 |
| 4  | 5        |  1             | 1          | 2017-02-21     |11:00:00   |13:00:00 |
|----|----------|----------------|------------|----------------|-----------|---------|

based on the booked_time data i'm running a inner join query to get the available time for particular date

my query is

SELECT DISTINCT time_data.vtime FROM booked_time INNER JOIN time_data ON  time_data.vtime>=booked_time.endtime OR time_data.vtime<booked_time.starttime WHERE facility_id='1' AND court='1' AND on_date='2017-02-20'

but its showing all the 15 records time. It should ignore the 08:00:00,09:00:00,11:00:00,12:00:00,13:00:00,14:00:00,15:00:00 hours.

How to get only available time ? with these tables or i need to change data table structure ?

Upvotes: 0

Views: 44

Answers (2)

Theo
Theo

Reputation: 1633

SELECT 
  time_data.* 
FROM time_data
LEFT JOIN booked_time ON 
  time_data.vtime BETWEEN booked_time.starttime AND booked_time.endtime
  AND booked_time.facility_id='1' 
  AND booked_time.court='1' 
  AND booked_time.on_date='2017-02-20'
WHERE 
  ISNULL(booked_time.id)

This first joins the table using LEFT JOIN so that it includes records in time_data that do not have any joined records. It then uses the WHERE To exclude any rows where there are records joined.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I think the correct logic you want is:

SELECT t.vtime
FROM time_data t LEFT JOIN
     booked_time b      
     ON t.vtime >= b.starttime AND
        t.vtime < b.endtime AND
        b.facility_id = '1' AND b.court = '1' AND
        b.on_date = '2017-02-20'
WHERE b.vtime IS NULL

This finds any matches and removes those, using a LEFT JOIN and comparison to NULL.

The alternative using NOT EXISTS seems a more natural way to do this:

select t.vtime
from time_data t
where not exists (select 1
                  from booked_time b      
                  where t.vtime >= b.starttime and
                        t.vtime < b.endtime and
                        b.facility_id = '1' and b.court = '1' and
                        b.on_date = '2017-02-20'
                 );

Upvotes: 1

Related Questions