Обсуждение: 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).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.
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', '[]').
I tried insert into availability values ('product x', '[2018-02-02,2018-03-01]'::
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