How to check if the start and end time that you want to plan for an activity aren't already pla-CodePudding

I need to make a query that checks if the start and endtime that a user wants to plan something aren't already planned

I currently have this query:

select *
from planned_activities
where 
    user_id = 161
and
    '2022-01-11 17:36:00' between start_time and end_time
or '2022-01-11 18:36:00' between start_time and end_time
or ('2022-01-11 17:36:00' <= start_time and '2022-01-13 18:36:00' >= end_time);

I find it hard to explain but I basicly want to return the data from the other planned activity if the planning isn't possible.

CodePudding user response：

SELECT EXISTS ( SELECT NULL
                FROM planned_activities
                WHERE @current_user_id = planned_activities.user_id 
                  AND @planned_activity_start < planned_activities.end_time
                  AND @planned_activity_end > planned_activities.start_time ) row_exists

The query checks does the row for current user which overlaps with entered time range exists. Returns one row with one column row_exists, possible values are 1 (the overlapping is found) or 0 (entered time range is free).

If adjacent time ranges are not allowed too then use weak comparing operators.