Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL); - Mailing list pgsql-bugs

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?
diff --git a/src/backend/utils/adt/timestamp.c
b/src/backend/utils/adt/timestamp.c
index ed03c50a..5e69e258 100644
--- a/src/backend/utils/adt/timestamp.c
+++ b/src/backend/utils/adt/timestamp.c
@@ -5992,6 +5992,10 @@ interval_part_common(PG_FUNCTION_ARGS, bool retnumeric)
                                intresult = tm->tm_mday;
                                break;

+                       case DTK_WEEK:
+                               intresult = (tm->tm_mday  - 1) / 7 + 1;
+                               break;
but I am not sure not sure how to write the doc.

On Sun, Feb 18, 2024 at 10:19 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> jian he <jian.universality@gmail.com> writes:
> > you already mentioned "Not all fields are valid for every input data type".
> > interval data type don't even have a unit "quarter",
> > so the following should generate an error?
> > select extract(quarter from interval '2011 year 12 month 48 hour
> > 1005min 2 sec 11 ms');
>
> I'm not especially persuaded by that reasoning.  Intervals don't have
> century or millisecond fields either, but we allow extracting those.
>
> If your argument is that we shouldn't allow it because we don't take
> the input INTERVAL '1 quarter', I'd be much more inclined to add that
> as valid input than to take away existing extract functionality.
> But I'm dubious about the proposition that extract's list of valid
> fields should exactly match the set of allowed input units.  The
> semantics aren't really the same (as per the '80 minutes' example)
> so such a restriction doesn't seem to have much basis in reality.
>

in interval_part_common:
case DTK_QUARTER:
intresult = (tm->tm_mon / 3) + 1;
break;

in timestamp_part_common:
case DTK_QUARTER:
intresult = (tm->tm_mon - 1) / 3 + 1;
break;

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.

for example:
drop table s;
create table s(a interval);
insert into s select ( g * 1000 || 'year ' ||  g || 'month ' || g || '
day ' || g || 'hour ' || g || 'min ' || g || 'sec' )::interval
from generate_series(-20, 20) g;

select
        extract(century from a) as century,
        extract(millennium from a) as millennium,
        extract(decade from a) as decade,
        extract(year from a) as year,
        extract(quarter from a) as quarter,
        extract(month from a) as mon,
        extract(day from a) as day,
        extract(hour from a) as hour,
        extract(min from a) as min,
        extract(second from a) as sec,
        extract(microseconds from a) as microseconds
        -- a
from s order by 2 asc;