CODEWITHSUNDEEP
postgresql
Simon
Simon

Reputation: 6462

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

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?

Upvotes: 10

Views: 3651

Answers (2)

TmTron
TmTron

Reputation: 19401

Some additions to Patricks answer:

  • Information if the lower/upper bound is included or excluded
    • the range type explicitly includes this information and it can be different per row
  • NULL
  • Cannot get date from empty tsrange
    • select lower(tsrange('2020-12-01', '2020-12-01')); returns NULL
    • this was a gotcha for me: I don't know if it's possible to get the start/end date
  • updating
  • tsrange will throw an error when the start is lower than the end 
    select tsrange('2020-12-20', '2020-12-19'); 
-- [22000] ERROR: range lower bound must be less than or equal to range upper bound
  • keep in mind, that the range types may not be supported by some drivers/libraries/tools. e.g. pg-promise, node-postgres have no build-in support. But there are external packages to add support, e.g. node-pg-range

Upvotes: 9

Patrick
Patrick

Reputation: 32254

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);

Upvotes: 12

Related Questions