daterange() is ignoring 3rd boundaries argument
select daterange('2022-11-01'::date, '2023-01-31'::date, '()')
returns
[2022-11-02,2023-01-31) which is deafult of [)
instead of
(2022-11-02,2023-01-31)
Version
PostgreSQL 14.6 (Ubuntu 14.6-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
from manual: 8.17.6. Constructing Ranges and Multiranges
Each range type has a constructor function with the same name as the
range type. Using the constructor function is frequently more convenient
than writing a range literal constant, since it avoids the need for
extra quoting of the bound values. The constructor function accepts two
or three arguments. The two-argument form constructs a range in standard
form (lower bound inclusive, upper bound exclusive), while the
three-argument form constructs a range with bounds of the form specified
by the third argument.
—
with best regards
Mag. Robert Kofler / Managing Partner
MARKSIM SERVICES GMBH
A-1220 Vienna / Austria, Oberdorfstrasse 9/1/15
https://statshop.marksim.net
rk@marksim.net
MARKSIM^® Conjoint And Market Simulation — We Simulate Future Market
Success
—
On Mon, Nov 28, 2022 at 5:16 PM Robert KOFLER <rk@marksim.org> wrote:
select daterange('2022-11-01'::date, '2023-01-31'::date, '()')
returns
[2022-11-02,2023-01-31) which is deafult of [)
instead of
(2022-11-02,2023-01-31)
You need to look at the boundary symbol AND the actual lower bound date.
Then read the following about discrete range types for what is happening
here:
https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-DISCRETE
from manual: 8.17.6. Constructing Ranges and Multiranges
Yes, that tells you how to take "text" and turn it into a datum of type
*range. Is discusses input only, not output. Output depends on the
specific type and, as noted above, in particular whether it is discrete or
continuous.
David J.
Robert KOFLER <rk@marksim.org> writes:
select daterange('2022-11-01'::date, '2023-01-31'::date, '()')
returns
[2022-11-02,2023-01-31) which is deafult of [)
instead of
(2022-11-02,2023-01-31)
It's not "ignoring" the endpoint argument, because you get different
results from
regression=# select daterange('2022-11-01'::date, '2023-01-31'::date, '()');
daterange
-------------------------
[2022-11-02,2023-01-31)
(1 row)
regression=# select daterange('2022-11-01'::date, '2023-01-31'::date, '[)');
daterange
-------------------------
[2022-11-01,2023-01-31)
(1 row)
regression=# select daterange('2022-11-01'::date, '2023-01-31'::date, '[]');
daterange
-------------------------
[2022-11-01,2023-02-01)
(1 row)
As explained somewhere in the fine manual (though not in the exact spot
you're reading), for ranges over discrete types such as dates, we prefer
to normalize to the '[)' endpoint conventions. That's not possible for
continuous types such as floats or timestamps, so in those cases we
leave the endpoint specs alone.
If that really annoys you, you can make your own range type over
dates that lacks a "canonical" function. But it's not a bug;
it's operating as intended.
regards, tom lane