Best database system to store and compare date ranges

database-recommendationdaterange-types

I'm working on a project which is dealing with a lot of date ranges.
I'm starting completely from scratch; there is no existing code, infrastructure or database. Therefore, I'm searching for the best technology for each job.

I need to store a lot of date ranges in a database. Later on I would like to compare those data ranges to find overlaps.

I´ve already read about elasticsearch and influxdb.

Do you know any database system which is specialized to store and work with a great amount of date ranges?

The date ranges could be stored as single days or as range like from 01.01.2016 to 08.01.2016. I don't want to store any other data besides an identifier, to identify each date range record.

Best Answer

PostgreSQL has the datatypes daterange, tsrange (timestamp range) and tstzrange (time stamp with time zone range), along with a number of operators and functions to process, cast and extract information from them.

It also has EXCLUSION constraints, which can be used to ensure no two rows have overlapping ranges. E.g.

CREATE TABLE reservation (
  room_id int not null,
  customer_id int not null,
  during tsrange not null,
  EXCLUDE USING gist (room_id, during WITH &&)
);

The above table would not allow the same room having overlapping reservations.

Related Question