PostgreSQL: Free time slot algorithm

Question

I have a table with some time slots in it, example:

#id datet           userid  agentid duration    
+=======================================================+
|1  |2013-08-20 08:00:00    |-1 |3  |5  
|2  |2013-08-20 08:05:00    |-1 |3  |5  
|3  |2013-08-20 08:10:00    | 3 |3  |5  
|4  |2013-08-20 08:15:00    |-1 |3  |5  
|5  |2013-08-20 08:20:00    |-1 |3  |5  
|6  |2013-08-20 08:25:00    |-1 |3  |5  
|7  |2013-08-20 08:30:00    |-1 |3  |5  
|8  |2013-08-20 08:05:00    |-1 |7  |15 
|9  |2013-08-20 08:20:00    |-1 |7  |15 
+=======================================================+

In the above example, the user wit id 3 has a slot at 8:10. (if userid = -1, it means it is a free slot). He has an appointment with agent 5. For example, now user 3 would like another timeslot, but this time with agent 7. So, the algorithm should keep only the free slots for agentid 7 and the possible slots wich doesn't overlap. This would mean, only the 9th record would be a solution in this case. (But maybe in another case, there are multiple solutions). Another thing, a user can only have one appointment with the same agent.

Any ideas how to implement this? I was thinking with the OVERLAPS operator, but can't figure it out how to do so.

Consider making this simpler and easier to read. From experience, SO people rarely answer/help out on questions that look overly complicated and verbose. — eatonphil, Aug 21 '13 at 15:07
Think about it the other way around. You want all the possible timeslots EXCEPT those that overlap. — Richard Huxton, Aug 21 '13 at 15:09
@RichardHuxton Indeed, but I've no idea how to do that. That's the whole point of this topic. How do I get all the overlapping moments? — Write Down, Aug 21 '13 at 15:13
*"He has an appointment with agent 5. "* How do you know that? The only 5 I see is for duration. — Mike Sherrill 'Cat Recall', Aug 21 '13 at 15:19

score 1 · Accepted Answer · answered Aug 21 '13 at 15:13

Try something like:

select *
from  time_slots ts
where agentid = 7 -- or any agent
  and userid = -1 -- it is free
  and not exists (select 1 -- and overlaping interval does not exist
                 from time_slots ts_2
                 where ts_2.userid <> -1 -- not free
                   and (ts.datet, ts.datet + interval '1 hour' * ts.duration) OVERLAPS
                       (ts_2.datet, ts_2.datet + interval '1 hour' * ts_2.duration))

PostgreSQL: Free time slot algorithm

1 Answers1