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

On Tue, Jul 9, 2024 at 1:03 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I took another look at this issue and got annoyed by the fact that the
> proposed coding for "quarter" still doesn't satisfy the rule that
> the output for a negative interval should be the negative of the
> output for the sign-reversed interval.  Specifically, if the month
> field is zero, the v2 patch always emits 1:
>
> regression=# select extract(quarter from interval '1 day');
>  extract
> ---------
>        1
> (1 row)
>
> regression=# select extract(quarter from interval '-1 day');
>  extract
> ---------
>        1
> (1 row)
>
> We could fix that by examining the sign of the lower-order fields
> when month is zero, as in the v3 patch attached.  However, I'm not
> at all sure this is really better than v2.  Notably, it makes the
> documentation's statement that the result is "the month field
> divided by 3 plus 1" even more incomplete.  I still don't really
> want to go into details about the behavior for negative intervals.
> OTOH if we did do that, I'd rather write a blanket statement
> about the result being the negative of the result for a positive
> interval.
>
> Thoughts?
>
>                         regards, tom lane
>


+       <para>
+        For <type>interval</type> values, the week field is simply the number
+        of integral days divided by 7.
+       </para>


+SELECT EXTRACT(WEEK FROM INTERVAL '13 days 24 hours');
+<lineannotation>Result: </lineannotation><computeroutput>1</computeroutput>

not sure the doc example will vividly demonstrate the explanation ("integral")
or confuse people, given that
SELECT EXTRACT(WEEK FROM INTERVAL '14 days');
returns 2.

and
SELECT  INTERVAL '14 days' = INTERVAL '13 days 24 hours';
is true.