Hacker News new | ask | show | jobs
by shagie 3339 days ago
Because everyone who has been constrained to other databases has done things like "SELECT start, end FROM reservations" and then tried to write the where clause to find the range, had bugs (this person reserved a room for longer than the expected time).

And the end of the day, they ended up selecting all the start and end dates and then building their own interval tree ( https://en.wikipedia.org/wiki/Interval_tree ) and doing overlap detection themselves.

If they were using a database with good stored procedure support and had the skills to write the stored procedure or function in the database, they might then fight with writing the overlap detection there... but they likely found it a profoundly unpleasant experience.

And then here's PostgreSQL. It has it native. Hours, days, weeks of time spent writing, testing, debugging and otherwise fighting with this problem now suddenly become something along the lines of "SELECT isempty(daterange('2017-04-30', '2017-05-07')) FROM reservations" or something to that effect.

It represents a significant time savings when you need it for things that are more interesting than reimplementing data structures that aren't in the standard libraries.
1 comments
walrus 3339 days ago
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!
link