Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);

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=# select n, extract(quarter from interval '1 mon' * n) from generate_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=# select n, extract(quarter from interval '1 mon' * n) from generate_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