CODEWITHSUNDEEP
postgresql
cleong
cleong

Reputation: 7636

PostgreSQL: How to get a daterange with inclusive end date?

Is there any way to tell PostgreSQL to output a daterange with an inclusive end date?

For example, if I run this:

SELECT '[2016-09-01,2016-09-10]'::daterange

The output is:

[2016-09-01,2016-09-11)

This is really annoying, since what I get back from the database does not match what I put in.

Upvotes: 4

Views: 3325

Answers (1)

klin
klin

Reputation: 121784

This behavior is intentional and has solid reasons. The daterange is a discrete range type what means that it changes all input values to their canonical form. This makes it possible to compare the same ranges in various forms:

select '[2016-09-01,2016-09-10]'::daterange = '[2016-09-01,2016-09-11)'::daterange;
-- yields true

You could create your own range type:

create type mydaterange as range
   (subtype=date,
    subtype_diff=daterange_subdiff);

select '[2016-09-01,2016-09-10]'::daterange, '[2016-09-01,2016-09-10]'::mydaterange

        daterange        |       mydaterange       
-------------------------+-------------------------
 [2016-09-01,2016-09-11) | [2016-09-01,2016-09-10]
(1 row)

but it makes little sense as in this case

select '[2016-09-01,2016-09-10]'::mydaterange = '[2016-09-01,2016-09-11)'::mydaterange;
-- yields false!

Read about Discrete Range Types.

Upvotes: 4

Related Questions