From: Tom Lane <tgl@sss.pgh.pa.us>
To: jian he <jian.universality@gmail.com>
cc: Francisco Olarte <folarte@peoplecall.com>,
        Michael Bondarenko <work.michael.2956@gmail.com>,
        pgsql-bugs@lists.postgresql.org, dgrowleyml@gmail.com,
        Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
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>
Comments: In-reply-to jian he <jian.universality@gmail.com>
	message dated "Tue, 20 Feb 2024 11:56:29 +0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <222869.1708458150.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 20 Feb 2024 14:42:30 -0500
Message-ID: <222870.1708458150@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/222870.1708458150%40sss.pgh.pa.us>
Precedence: bulk

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