Hacker News new | comments | show | ask | jobs | submit login

Specifically, here's how that would be done in Postgres:

  create table reservation1 (duration tstzrange);
  select * from reservation1 where duration && '[2017-04-30, 2017-05-07]';
In a database without range types, it would look like this:

  create table reservation2 (start timestamptz, stop timestamptz, check (start <= stop));
  select * from reservation2 where '2017-04-30' <= stop and start <= '2017-05-07';
Personally, I don't think range types by themselves are anything special. However, combined with indexes and exclusion constraints, they're really great:

  create table reservation1 (duration tsrange, exclude using gist (duration with &&));
  insert into reservation1 values ('[2017-04-30, 2017-05-07]');  -- ok
  insert into reservation1 values ('[2017-05-10, 2017-05-12]');  -- ok
  insert into reservation1 values ('[2017-05-05, 2017-05-08]');  -- error!
Here's the difference indexes made when retrieving all the scheduling conflicts with a fixed interval (500k rows):

  No index (start, stop)        123ms
  Btree index (start, stop)     29ms
  GIST index (duration)         0.2ms
Quite a difference!



Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | DMCA | Apply to YC | Contact

Search: