From: Tom Lane <tgl@sss.pgh.pa.us>
To: jian he <jian.universality@gmail.com>
Cc: Francisco Olarte <folarte@peoplecall.com>
Cc: Michael Bondarenko <work.michael.2956@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org, dgrowleyml@gmail.com, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
Date: Tue, 20 Feb 2024 14:42:30 -0500
Message-ID: <222870.1708458150@sss.pgh.pa.us> (raw)
In-Reply-To: <CACJufxHL0g73FPaRS+OGSYWJ0YbP6nEdVK79=wjL-TKU0_heDw@mail.gmail.com>
References: <18348-b097a3587dfde8a4@postgresql.org>
<CAApHDvr_-05ce6FaLM0P=hJBdV=Ht3=sdk_Eq__7YOf+fs5x9g@mail.gmail.com>
<CACJufxHYQSKNnt8rS7GxvK04wf75nw_ugMaibFVVfrcdVMmF1Q@mail.gmail.com>
<CAF_O+z1FU5GN=LnW50QjANGVB8+6VxyK1JHa8fE-Xs205MFuqw@mail.gmail.com>
<CA+bJJbw6q=6no1Hf67FraYGMo12asWCa7yw=J0zg_ELbivX0gw@mail.gmail.com>
<3709307.1708201825@sss.pgh.pa.us>
<CACJufxGB4MD6vRz3jVGK6kza-6fuDgfW2jC_1S2+MwcQYGkgSA@mail.gmail.com>
<3749306.1708222748@sss.pgh.pa.us>
<CACJufxHL0g73FPaRS+OGSYWJ0YbP6nEdVK79=wjL-TKU0_heDw@mail.gmail.com>
jian he <jian.universality@gmail.com> writes:
> On Sun, Feb 18, 2024 at 2:14 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> (Parenthetically, one case that perhaps is surprising is
>> ERROR: unit "week" not supported for type interval
>> Why not just return the day field divided by 7?)
> seems pretty simple?
Hm, maybe, but does this behave desirably for zero or negative days?
> So in section 9.9.1. EXTRACT, date_part
> we may need to document extract(quarter from interval) case.
> intervals can be negative, which will make the issue more complicated.
> except the "quarter" field , EXTRACT other fields from intervals, the
> output seems sane.
Yeah, I see what you mean: the output for negative month counts is
very bizarre, whereas other fields seem to all produce the negative
of what they'd produce for the absolute value of the interval.
We could either try to fix that or decide that rejecting "quarter"
for intervals is the saner answer.
I went ahead and pushed the docs changes after adding more explicit
descriptions of interval's behavior for the field types where it
seemed important. If we make any changes to the behavior for
week or quarter fields, ISTM that should be a HEAD-only change.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, jian.universality@gmail.com, folarte@peoplecall.com, work.michael.2956@gmail.com, peter.eisentraut@enterprisedb.com
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
In-Reply-To: <222870.1708458150@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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