From: | Paul Jungwirth <pj(at)illuminatedcomputing(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to get an inclusive interval when using daterange |
Date: | 2018-04-03 17:12:52 |
Message-ID: | ede1f883-4dbd-9035-6543-d35abce46209@illuminatedcomputing.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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://illuminatedcomputing.com/posts/2016/06/inet-range/
--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | PegoraroF10 | 2018-04-03 17:47:40 | single sql, multiple schemas, one result set |
Previous Message | hmidi slim | 2018-04-03 16:40:28 | Re: How to get an inclusive interval when using daterange |