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

On Wed, Feb 21, 2024 at 4:56 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> I wrote:
> > jian he <jian(dot)universality(at)gmail(dot)com> writes:
> >> On Sun, Feb 18, 2024 at 2:14 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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
>

for `week`, we can do following for the doc:

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e5fa82c1..a21eb9f8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10422,7 +10422,7 @@ SELECT EXTRACT(SECOND FROM TIME '17:12:28.5');
The number of the <acronym>ISO</acronym> 8601 week-numbering week of
the year. By definition, ISO weeks start on Mondays and the first
week of a year contains January 4 of that year. In other words, the
- first Thursday of a year is in week 1 of that year.
+ first Thursday of a year is in week 1 of that year. For
<type>interval</type> values, divide the number of days by 7.

Actually, it's not totally correct, since "the number of days is a
numeric value. need to cast "the number of days" to int.

for positive interval value, we can
+ For positive <type>interval</type> values, divide the number of days
by 3 then plus 1.
I don't know how to write the documentation for the `quarter` when
it's negative.