I wrote:
> jian he <jian.universality@gmail.com> writes:
>> On Sun, Feb 18, 2024 at 2:14 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> (Parenthetically, one case that perhaps is surprising is
>>> ERROR: unit "week" not supported for type interval
>>> Why not just return the day field divided by 7?)
>> seems pretty simple?
> Hm, maybe, but does this behave desirably for zero or negative days?
>> So in section 9.9.1. EXTRACT, date_part
>> we may need to document extract(quarter from interval) case.
>> intervals can be negative, which will make the issue more complicated.
>> except the "quarter" field , EXTRACT other fields from intervals, the
>> output seems sane.
> Yeah, I see what you mean: the output for negative month counts is
> very bizarre, whereas other fields seem to all produce the negative
> of what they'd produce for the absolute value of the interval.
> We could either try to fix that or decide that rejecting "quarter"
> for intervals is the saner answer.
After fooling with these cases for a little I'm inclined to think
we should do it as attached (no test or docs changes yet).
regards, tom lane
diff --git a/src/backend/utils/adt/timestamp.c b/src/backend/utils/adt/timestamp.c
index ed03c50a6d..7177c1a62f 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -5992,12 +5992,19 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
intresult = tm->tm_mday;
break;
+ case DTK_WEEK:
+ intresult = tm->tm_mday / 7;
+ break;
+
case DTK_MONTH:
intresult = tm->tm_mon;
break;
case DTK_QUARTER:
- intresult = (tm->tm_mon / 3) + 1;
+ if (tm->tm_year >= 0)
+ intresult = (tm->tm_mon / 3) + 1;
+ else
+ intresult = -((-tm->tm_mon / 3) + 1);
break;
case DTK_YEAR:
