From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruce Momjian <bruce@momjian.us>
Cc: jian he <jian.universality@gmail.com>
Cc: Francisco Olarte <folarte@peoplecall.com>
Cc: Michael Bondarenko <work.michael.2956@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org, dgrowleyml@gmail.com, Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
Date: Fri, 16 Aug 2024 12:06:35 -0400
Message-ID: <761517.1723824395@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, bruce@momjian.us, jian.universality@gmail.com, folarte@peoplecall.com, work.michael.2956@gmail.com, peter.eisentraut@enterprisedb.com
Subject: Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);
In-Reply-To: <761517.1723824395@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox