Re: How to get an inclusive interval when using daterange

On 04/03/2018 09:40 AM, hmidi slim wrote:
> I tried insert into availability values ('product x', 
> '[2018-02-02,2018-03-01]'::daterange); and I got the same result such 
> as insert into availability values ('product x', daterange('2018-02-02', 
> '2018-03-01', '[]').

Yes, those are equivalent ways of constructing the same daterange.

If you really want a closed/closed daterange, you'll need to create your 
own type. I don't really recommend that, but you can do it. (Using 
close/open is the standard because it's so convenient for 
combining/comparing ranges.)

It's easy to create a type without a canonical function, e.g.:

     CREATE FUNCTION date_minus(date1 date, date2 date)
     RETURNS float AS $$
     SELECT cast(date1 - date2 as float);
     $$ LANGUAGE sql immutable;

     CREATE TYPE daterange2 AS range
     (subtype = date, subtype_diff = date_minus);

Then you can say:

     SELECT daterange2('2018-01-01', '2018-03-01', '[]');

This is not great though, because without a canonical function Postgres 
doesn't know that [x,y] is equal to [x,y+1). If you prefer to have a 
canonical function, you'll need to write one in C. (It's easy but you 
won't be able to install it on a managed service like AWS RDS.) It might 
help to read these and the code they link to (The second one is by me.):

https://stackoverflow.com/questions/29895077/how-to-create-a-new-date-range-type-with-included-upper-bound-in-postgres

https://illuminatedcomputing.com/posts/2016/06/inet-range/

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com