From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | 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-07-12 16:35:19 |
Message-ID: | CACJufxFSK8LOfME5LfsT+Ji2xV+h+JjH2QT=one=zCyz2b=H8w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs pgsql-hackers |
On Tue, Jul 9, 2024 at 1:03 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2024-07-13 09:00:00 | BUG #18536: Using WITH inside WITH RECURSIVE triggers a "shouldn't happen" error |
Previous Message | Alvaro Herrera | 2024-07-12 12:36:55 | Re: BUG #18156: Self-referential foreign key in partitioned table not enforced on deletes |
From | Date | Subject | |
---|---|---|---|
Next Message | Nathan Bossart | 2024-07-12 16:49:38 | Re: Remove dependence on integer wrapping |
Previous Message | Nathan Bossart | 2024-07-12 16:19:05 | Re: Allow non-superuser to cancel superuser tasks. |