CODEWITHSUNDEEP
postgresql
Peck
Peck

Reputation: 842

Postgresql Modeling Ranges

I'm looking to model a group of users that provider various services that take various times and hoping to build on the relatively new ranges datetypes that Postgresql supports to make things a lot cleaner.

Bookings:
 user_id|integer
 time|tsrange
 service_id|integer

Services:
 user_id|integer
 time_required|integer #in hours

Users:
 id|integer

Services vary between users, some might be identical but takes one user 2 hours and another just 1 hour.

Searching for bookings that occur within, or not within a given time period are easy. I'm having trouble figuring out how best I would get all the users who have time available on a given day to perform one or more of their services.

I think I need to find the inverse of their booked ranges, bound by 6am/8pmn on a given day and then see if the largest range within that set will fit their smallest service, but figuring out how express that in SQL is eluding me.

Is it possible to do inverses of ranges, or would their be a better way to approach this?

Upvotes: 1

Views: 88

Answers (1)

Denis de Bernardy
Denis de Bernardy

Reputation: 78463

The question isn't entirely clear, but if I get it right you're look for the lead() or lag() window function to compute available time slots. If so, this should put you on the right track:

select bookings.*, lead("time") over (order by "time") as next_booking_time
from bookings

http://www.postgresql.org/docs/current/static/tutorial-window.html

Upvotes: 1

Related Questions