pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feeddaterange() is ignoring 3rd boundaries argument
3+ messages / 3 participants
[nested] [flat]
* daterange() is ignoring 3rd boundaries argument
@ 2022-11-28 23:50 Robert KOFLER <rk@marksim.org>
2022-11-29 00:35 ` Re: daterange() is ignoring 3rd boundaries argument David G. Johnston <david.g.johnston@gmail.com>
2022-11-29 00:42 ` Re: daterange() is ignoring 3rd boundaries argument Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Robert KOFLER @ 2022-11-28 23:50 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
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
—
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: daterange() is ignoring 3rd boundaries argument
2022-11-28 23:50 daterange() is ignoring 3rd boundaries argument Robert KOFLER <rk@marksim.org>
@ 2022-11-29 00:35 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: David G. Johnston @ 2022-11-29 00:35 UTC (permalink / raw)
To: rk@marksim.org; +Cc: pgsql-bugs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: daterange() is ignoring 3rd boundaries argument
2022-11-28 23:50 daterange() is ignoring 3rd boundaries argument Robert KOFLER <rk@marksim.org>
@ 2022-11-29 00:42 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2022-11-29 00:42 UTC (permalink / raw)
To: rk@marksim.org; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2022-11-29 00:42 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-11-28 23:50 daterange() is ignoring 3rd boundaries argument Robert KOFLER <rk@marksim.org>
2022-11-29 00:35 ` David G. Johnston <david.g.johnston@gmail.com>
2022-11-29 00:42 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox