SQL - Check if overlapping working hour exist

Question

I have following database schema (where day value 0=mon,1=tue,2=wed and so on)

And the following input

{
    "day": 3,
    "start_time": "15:00",
    "end_time": "17:30"
},
{
    "day": 5
    "start_time": "08:00",
    "end_time": "12:00"
}

I want to find if input value contains any overlapping record from the database. (note that in my case, I can have multiple start_time, end_time for a given day)

Here is what I tried.

SELECT
    COUNT(id) AS total_count
FROM
    working_hour
WHERE (
    (day = 3 AND (start_time <= '15:00' AND end_time >= '17:30')) ||
    (day = 5 AND (start_time <= '08:00' AND end_time >= '12:00'))
) AND user_id = 1;

This return me total count of 0 whereas I am expecting 1 because on day = 3 we have a matching overlapping record with id 5

Where am I going wrong here?

Thank you.

This could get hairy. Is there any reason why you are storing the day and time in separate columns? — Tim Biegeleisen, Apr 07 '19 at 05:04
I need to store the working hour of user, the application I am working on allows to book a calendar for the user based on availability hence the user has to first define the availability. — Ibrahim Azhar Armar, Apr 07 '19 at 05:11
Isn't it normal to define and store business hours the way I am storing? check these examples https://stackoverflow.com/questions/19545597/way-to-store-various-shop-opening-times-in-a-database, https://stackoverflow.com/questions/4464898/best-way-to-store-working-hours-and-query-it-efficiently — Ibrahim Azhar Armar, Apr 07 '19 at 05:12
I am willing to know if there are any better/alternative approach. — Ibrahim Azhar Armar, Apr 07 '19 at 05:12
Can an input range span across multiple days, or is it guaranteed that an input would always be within a single day? — Tim Biegeleisen, Apr 07 '19 at 05:14
Input range can be more than one. I added only one for now to make it bit simpler. — Ibrahim Azhar Armar, Apr 07 '19 at 05:16
You should instead just show us the real problem. As it stands now, you will end up receiving an incomplete answer. — Tim Biegeleisen, Apr 07 '19 at 05:18

score 1 · Accepted Answer · answered Apr 07 '19 at 05:20

1

I believe the logic you want is:

SELECT COUNT(*) AS total_count
FROM working_hour
WHERE user_id = 1 AND
      ((day = 3 AND start_time <= '17:30' AND end_time >= '15:00') OR
       (day = 5 AND start_time <= '12:00' AND end_time >= '8:00')
      ) ;

answered Apr 07 '19 at 05:20

Gordon Linoff

1,242,037
58
646
786

SQL - Check if overlapping working hour exist

1 Answers1