From: Tom Lane <tgl@sss.pgh.pa.us>
To: 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: Tue, 20 Feb 2024 15:56:04 -0500
Message-ID: <270582.1708462564@sss.pgh.pa.us> (raw)
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>
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
Attachments:
[text/x-diff] interval-week-and-quarter-fixes.patch (667B, ../270582.1708462564@sss.pgh.pa.us/2-interval-week-and-quarter-fixes.patch)
download | inline diff:diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.cindex 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 retnumeric)
intresult = tm->tm_mday;
break;
+ case DTK_WEEK:+ intresult = tm->tm_mday / 7;+ break;+
case DTK_MONTH:
intresult = tm->tm_mon;
break;
case DTK_QUARTER:
- intresult = (tm->tm_mon / 3) + 1;+ if (tm->tm_year >= 0)+ intresult = (tm->tm_mon / 3) + 1;+ else+ intresult = -((-tm->tm_mon / 3) + 1);
break;
case DTK_YEAR:
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, 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: <270582.1708462564@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