What are the benefits of using Postgresql Daterange type instead of two Date fields?

Question

I'm working with PostgreSQL 9.4 and I discovered today the Daterange type. Until now I used a field startDateTime and an other field startEndTime, so what would be the benefits of using the Daterange type instead?

Answer 1

There is nothing that you can't do with a startDateTime and an endDateTime that you can do with a tsrange (or daterange for dates). However, there a quite a few operators on range types that make writing queries far more concise and understandable. Operators like overlap &&, containment @> and adjacency -|- between two ranges are especially useful for date and time ranges. A big bonus for range types is that you apply a gist index on them which makes searches much faster.

As an example, find all rows where an event takes place within a certain time range:

Start/end

SELECT *
FROM events
WHERE startDateTime >= '2016-01-01'::timestamp
  AND endDateTime < '2016-01-19'::timestamp;

Range

SELECT *
FROM events
WHERE startEndRange <@ tsrange('2016-01-01'::timestamp, '2016-01-19'::timestamp);