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

On Thu, Aug 15, 2024 at 10:45:58PM -0400, Bruce Momjian wrote:
> > 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?
> 
> I tested master, patch version 2 and patch version 3 with some sample
> extract() queires, attached.  I like patch version 2.  Patch version 3
> bothers me because "-600 days" is ignored if months is non-zero, and
> used for its sign for zero month values, which seems odd to me;  better
> to ignore it.

I think there are two more issues.  In patch version 3, when months is
zero and you check days, you should also check seconds if days is zero.

I think the other issue is that zero months is a valid Q1 value, since
months 0-2 are Q1;  from master:

    SELECT extract(quarter FROM interval '0 months');
     extract
    ---------
           1
    
    SELECT extract(quarter FROM interval '2 months');
     extract
    ---------
           1
    
    SELECT extract(quarter FROM interval '3 months');
     extract
    ---------
           2

so the idea that we should adjust the sign for zero months quarter
extract doesn't seem logical to me.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.