From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruce Momjian <bruce@momjian.us>
cc: jian he <jian.universality@gmail.com>,
        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: <Zr91xiAbtKw-kxZM@momjian.us>
References: <222870.1708458150@sss.pgh.pa.us>
 <270582.1708462564@sss.pgh.pa.us>
 <CACJufxFOjGpAQJ5EJ0=a1geERgdHW77SEdRyK8A_qwB3CVdwhQ@mail.gmail.com>
 <166456.1715117228@sss.pgh.pa.us>
 <CACJufxGFuZsj+31a204fFpxv_RCHA-u1h6WEBZ6qpjBVoio5dQ@mail.gmail.com>
 <476283.1715177448@sss.pgh.pa.us> <1079670.1720458208@sss.pgh.pa.us>
 <Zr69ZnXWlS_2_bvI@momjian.us> <Zr9vwnzROmfi_MI4@momjian.us>
 <755165.1723822675@sss.pgh.pa.us> <Zr91xiAbtKw-kxZM@momjian.us>
Comments: In-reply-to Bruce Momjian <bruce@momjian.us>
	message dated "Fri, 16 Aug 2024 11:52:38 -0400"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <761516.1723824395.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 16 Aug 2024 12:06:35 -0400
Message-ID: <761517.1723824395@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/761517.1723824395%40sss.pgh.pa.us>
Precedence: bulk

Bruce Momjian <bruce@momjian.us> writes:
> On Fri, Aug 16, 2024 at 11:37:55AM -0400, Tom Lane wrote:
>> But I'm starting to despair of reaching a solution that's actually
>> self-consistent.  Maybe we should leave the DTK_QUARTER behavior
>> alone, and content ourselves with adding DTK_WEEK.

> Well, I liked that -4 months actually was in -2 quarter.

Yeah.  On further reflection, I agree it's a bad idea for the
DTK_QUARTER computation to depend on anything but the months field.
So that lets out v3.  However, what we have historically is

regression=3D# select n, extract(quarter from interval '1 mon' * n) from g=
enerate_series(-12,12) n;
  n  | extract =

-----+---------
 -12 |       1
 -11 |      -2
 -10 |      -2
  -9 |      -2
  -8 |      -1
  -7 |      -1
  -6 |      -1
  -5 |       0
  -4 |       0
  -3 |       0
  -2 |       1
  -1 |       1
   0 |       1
   1 |       1
   2 |       1
   3 |       2
   4 |       2
   5 |       2
   6 |       3
   7 |       3
   8 |       3
   9 |       4
  10 |       4
  11 |       4
  12 |       1
(25 rows)

which is fine on the positive side but it's hard to describe the
results for negative months as anything but wacko.  The v2 patch
gives

regression=3D# select n, extract(quarter from interval '1 mon' * n) from g=
enerate_series(-12,12) n;
  n  | extract =

-----+---------
 -12 |      -1
 -11 |      -4
 -10 |      -4
  -9 |      -4
  -8 |      -3
  -7 |      -3
  -6 |      -3
  -5 |      -2
  -4 |      -2
  -3 |      -2
  -2 |      -1
  -1 |      -1
   0 |       1
   1 |       1
   2 |       1
   3 |       2
   4 |       2
   5 |       2
   6 |       3
   7 |       3
   8 |       3
   9 |       4
  10 |       4
  11 |       4
  12 |       1
(25 rows)

which is a whole lot saner.  So let's run with v2.

			regards, tom lane