CODEWITHSUNDEEP
sqlpostgresql
user1354934
user1354934

Reputation: 8841

How to find available workers given start and end time?

I'm trying to come up with a way to find workers who are available between a time range.

Here's what my simple table looks like:

create table work_hours (
  worker_id bigint,
  day int check( day > 0 and day < 8),
  starts_at time,
  ends_at time
)

How can I find workers that might be working between say "12:00" and "15:30"?

I tried using the BETWEEN operator on starts_at, but how can I check the range because a worker might be working between "09:00" and "17:00" which is between the range I want?

Upvotes: 2

Views: 102

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

You can use inequalities. This gives you more control over whether the boundaries are included or not included:

select w.*
from works w
where not exists (select 1
                  from worker_hours wh
                  where wh.worker_id = w.worker_id and
                        wh.starts_at < '15:30:00' and
                        wh.ends_at > '12:00:00'
                 );

This does not include the boundaries in the comparisons. It also assumes that there is a separate table with all worker ids -- which seems reasonable.

Upvotes: 1

GMB
GMB

Reputation: 222582

Postgres has a sophisticated date/time function called OVERLAPS.

The following query will give you ids of the workers that have no record in the work_hours table that overlap the 12:00 > 15;30 timeslot. This basically means that these workers are available all week long in the timeslot.

SELECT DISTINCT worker_id
FROM work_hours w
WHERE NOT EXISTS (
    SELECT 1
    FROM work_hours w1
    WHERE 
        w1.worker_id = w.worker_id
        AND (TIME '12:00:00', TIME '15:30:00') OVERLAPS (starts_at, ends_at)
)

Upvotes: 1

Related Questions