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: <222870.1708458150@sss.pgh.pa.us>
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>
 <222870.1708458150@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Tue, 20 Feb 2024 14:42:30 -0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <270496.1708462532.0@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 20 Feb 2024 15:56:04 -0500
Message-ID: <270582.1708462564@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/270582.1708462564%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <270496.1708462532.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit

I wrote:
> 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.

After fooling with these cases for a little I'm inclined to think
we should do it as attached (no test or docs changes yet).

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="interval-week-and-quarter-fixes.patch";
	charset="us-ascii"
Content-ID: <270496.1708462532.2@sss.pgh.pa.us>
Content-Description: interval-week-and-quarter-fixes.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/tim=
estamp.c
index ed03c50a6d..7177c1a62f 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -5992,12 +5992,19 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnum=
eric)
 				intresult =3D tm->tm_mday;
 				break;
 =

+			case DTK_WEEK:
+				intresult =3D tm->tm_mday / 7;
+				break;
+
 			case DTK_MONTH:
 				intresult =3D tm->tm_mon;
 				break;
 =

 			case DTK_QUARTER:
-				intresult =3D (tm->tm_mon / 3) + 1;
+				if (tm->tm_year >=3D 0)
+					intresult =3D (tm->tm_mon / 3) + 1;
+				else
+					intresult =3D -((-tm->tm_mon / 3) + 1);
 				break;
 =

 			case DTK_YEAR:

------- =_aaaaaaaaaa0--