Re: BUG #18348: Inconsistency with EXTRACT([field] from INTERVAL);

When testing I stumbled upon that too, but I thought no calculation was
happening in the interval field. However, it's different with the days and
months etc. It seems no calculation for day and month and more:

tpch=# select extract(day from interval '86400000 seconds');
extract
---------
0
(1 row)

tpch=# select extract(month from interval '86400000 seconds');
extract
---------
0
(1 row)

tpch=# select extract(year from interval '86400000 seconds');
extract
---------
0
(1 row)

But calculation is present for hour, and minutes and seconds (90061 sec is
1 day 1 hour 1 minute 1 second):

tpch=# select extract(minute from interval '90061 seconds');
extract
---------
1
(1 row)

tpch=# select extract(hour from interval '90061 seconds');
extract
---------
25
(1 row)

tpch=# select extract(second from interval '90061 seconds');
extract
----------
1.000000
(1 row)

The docs mention *The hour field (0–23)* for the hours, which is not true
because it's not the field at all, but the calculated amount, and the value
is not 0-23.

On Sat, Feb 17, 2024 at 3:48 AM jian he <jian(dot)universality(at)gmail(dot)com> wrote:

> in `9.9.1. EXTRACT, date_part`
> EXTRACT(field FROM source)
>
> I saw more inconsistencies with the doc when `source` is an interval.
>
> the `minute` field
> select extract(minute from interval '2011 year 16 month 35 day 48 hour
> 1005 min 71 sec 11 ms');
> select extract(minute from interval '2011 year 16 month 35 day 48 hour
> 1005 min 2 sec 11 ms');
> select extract(minute from interval '2011 year 16 month 35 day 48 hour
> 1005 min 2 sec 11 ms');
>
> the `hour` field:
> select extract(hour from interval '2011 year 16 month 35 day 48 hour
> 1005 min 71 sec 11 ms');
> select extract(hour from interval '2011 year 16 month 35 day 48 hour
> 1005 min 2 sec 11 ms');
> select extract(hour from interval '2011 year 16 month 35 day 48 hour
> 1005 min 71 sec 11111111111 ms');
>
> the `quarter` field:
> select extract(quarter from interval '2011 year 12 month 48 hour 1005
> min 2 sec 11 ms');
> SELECT EXTRACT(QUARTER FROM TIMESTAMP '2001-12-16 20:38:40');
>