From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | jian he <jian(dot)universality(at)gmail(dot)com>, Francisco Olarte <folarte(at)peoplecall(dot)com>, Michael Bondarenko <work(dot)michael(dot)2956(at)gmail(dot)com>, pgsql-bugs(at)lists(dot)postgresql(dot)org, dgrowleyml(at)gmail(dot)com, Peter Eisentraut <peter(dot)eisentraut(at)enterprisedb(dot)com> |
Subject: | Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); |
Date: | 2024-08-16 15:26:58 |
Message-ID: | Zr9vwnzROmfi_MI4@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-08-16 15:37:55 | Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); |
Previous Message | Tom Lane | 2024-08-16 14:44:25 | Re: TLS session tickets disabled? |
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2024-08-16 15:29:17 | Re: Make query cancellation keys longer |
Previous Message | Tom Lane | 2024-08-16 14:55:51 | Re: [PROPOSAL] : Disallow use of empty column name in (column_name '') in ALTER or CREATE of foreign table. |