How to get an inclusive interval when using daterange
Hi,
I have a table* availability* which contains 3 columns: * id, product_id
and period_availability(type daterange).*
When I insert a data into this table I use this query:
insert into availability values ('product x', daterange('2018-02-02',
'2018-03-01', '[]')
So I get a period like this: [2018-02-02, 2018-03-02)
In my app I tried to subtract a day from the period when I got it from
database.I'm using daterange and not tsrange because the daterange gives me
less execution time when I make tests with a huge number of data.So is
there any way to get an inclusive interval with daterange or I have to use
tsrange to get inclusive intervals?
hmidi slim <hmidi.slim2@gmail.com> writes:
When I insert a data into this table I use this query:
insert into availability values ('product x', daterange('2018-02-02',
'2018-03-01', '[]')
So I get a period like this: [2018-02-02, 2018-03-02)
Yup.
In my app I tried to subtract a day from the period when I got it from
database.I'm using daterange and not tsrange because the daterange gives me
less execution time when I make tests with a huge number of data.So is
there any way to get an inclusive interval with daterange or I have to use
tsrange to get inclusive intervals?
No, daterange will always canonicalize a range into '[)' format.
This is explained (perhaps not with adequate clarity) in
https://www.postgresql.org/docs/10/static/rangetypes.html#RANGETYPES-DISCRETE
The key reason why is to make it clearer which range specifications
are equal. For instance, it's not really clear whether
['2018-02-02','2018-03-01'] and ['2018-02-02','2018-03-02') represent
the same set of values --- they do if it's a daterange, but not if it's
a tsrange. Canonicalizing makes equal ranges look equal.
regards, tom lane
I tried it and I got the same result.
Import Notes
Reply to msg id not found: CADgbbqhc6sfWB4T_9ewCu7m3bHw0h-ivSd5wWb_D47fp35H=9w@mail.gmail.com
On 04/03/2018 07:35 AM, hmidi slim wrote:
I tried it and I got the same result.
Tried what?
--
Adrian Klaver
adrian.klaver@aklaver.com
HI,
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', '[]').*
Import Notes
Reply to msg id not found: CAMsqVxu5C1-cpOS10Gu7yoqo1G0csw63bUbLEUb6EWWoyKXp4w@mail.gmail.com
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@illuminatedcomputing.com