Mysql select users who are not busy between a specific time range

Question

I have a users table and tasks table. Users can have assigned tasks. Each task takes specific range of time. In the tasks table I have a user_id foreign key, date_start (unix timestamp), and date_stop (unix timestamp).

Let's say I want to assign new task which takes place today between 2:30am to 4:00pm to a random user who is free within those hours (doesn't have any task assigned to him which would overlap the new task).

What would be the proper SQL query to retrieve such users?

Possible duplicate of [Determine Whether Two Date Ranges Overlap](http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap) — Paul Spiegel, Feb 01 '17 at 23:17
@PaulSpiegel, it is not an overlap. This question is simpler, cause we know that `date_start` and `date_stop` can be linearly ordered (or, at least, should be). — Grzegorz Górkiewicz, Feb 01 '17 at 23:33
The answer in the linked question shows you, how to check if two ranges overlap. Now you just need a `NOT EXISTS` subquery. — Paul Spiegel, Feb 01 '17 at 23:41

Grzegorz Górkiewicz · Answer 1 · 2017-02-02T00:03:50.823

0

I assume that date_start is always before date_stop.

Basically I consider users with tasks that will begin after 4:00pm or with tasks that finished before 2:30am.

The limit 1 is, of course, responsible for only one user being taken from DB.

select * from (
  select * from users
  join tasks on users.user_id = tasks.user_id
  where unix_timestamp(str_to_date('Feb 02 2017 04:00pm', '%M %d %Y %h:%i%p')) < from_unixtime(tasks.date_start)
  or
  unix_timestamp(str_to_date('Feb 02 2017 02:30am', '%M %d %Y %h:%i%p')) > from_unixtime(tasks.date_stop)
) limit 1

edited Feb 02 '17 at 00:03

answered Feb 01 '17 at 23:18

Grzegorz Górkiewicz

3,993
4
19
35

This will find a user with a non overlaping task. But the same user could have another task assigned, which is overlaping. – Paul Spiegel Feb 02 '17 at 00:00

score 0 · Answer 2 · answered Feb 01 '17 at 23:51

select u.id
from users u
cross join tasks new_task
where new_task.id = :your_task_id
  and not exists (
    select *
    from tasks user_task
    where user_task.user_id = u.id
      and user_task.date_start < new_task.date_stop
      and user_task.date_stop  > new_task.date_start
)
limit 1

Mysql select users who are not busy between a specific time range

2 Answers2