Postgresql Modeling Ranges

Question

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?

For time_required, using an interval is probably a better data type. — Denis de Bernardy
– Denis de Bernardy, Commented Dec 11, 2013 at 20:51

Denis de Bernardy · Accepted Answer · 2013-12-11 20:59:41Z

1

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

answered Dec 11, 2013 at 20:59

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgresql Modeling Ranges

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related