From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Wetmore, Matthew (CTR)" <Matthew.Wetmore@evernorth.com>
cc: Francisco Olarte <folarte@peoplecall.com>,
        Michael Bondarenko <work.michael.2956@gmail.com>,
        "pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>,
        "dgrowleyml@gmail.com" <dgrowleyml@gmail.com>,
        Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
In-reply-to: <2219fd69a3024149af5adc31e14b25d7@evernorth.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>
 <222870.1708458150@sss.pgh.pa.us> <270582.1708462564@sss.pgh.pa.us>
 <CACJufxFOjGpAQJ5EJ0=a1geERgdHW77SEdRyK8A_qwB3CVdwhQ@mail.gmail.com>
 <166456.1715117228@sss.pgh.pa.us>
 <2219fd69a3024149af5adc31e14b25d7@evernorth.com>
Comments: In-reply-to "Wetmore, Matthew (CTR)" <Matthew.Wetmore@evernorth.com>
	message dated "Tue, 07 May 2024 22:09:34 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <473943.1715176550.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 08 May 2024 09:55:50 -0400
Message-ID: <473944.1715176550@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/473944.1715176550%40sss.pgh.pa.us>
Precedence: bulk

"Wetmore, Matthew  (CTR)" <Matthew.Wetmore@evernorth.com> writes:
> Devils advocating here, feel free to ignore.
> Is there a real need for a negative month?  Sounds like high level this =
could be disastrous if I screw up the syntax. (Ah, memories of DD)

What are you objecting to the "need for"?  That intervals can store
negative months at all?  I think that ship sailed a couple decades
ago.  It's hard to use interval as the output of, say,
timestamp minus timestamp if it refuses to allow negative values.

The next fallback position perhaps could be that extract(quarter ...)
could throw error for negative input, but that seems like mostly a
foot-gun.  We've striven elsewhere to not have it throw error, even
if there's not any very sane choice to make.  For instance, these
are pre-existing behaviors:

regression=3D# select extract(quarter from interval 'infinity');
 extract =

---------
        =

(1 row)

regression=3D# select extract(quarter from interval '-infinity');
 extract =

---------
        =

(1 row)

Maybe there's a case for returning null for "quarter" for any negative
months value, but that seems inconsistent with other behaviors of
extract().  The pattern I see for finite values is that negating
the input interval negates each output of extract().

			regards, tom lane