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(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.