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

From: Michael Bondarenko <[email-masked]>
Date: Sat, 17 Feb 2024 10:00:39 +0200

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 <[email-masked]> 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');
>

---

From: Francisco Olarte <[email-masked]>
Date: Sat, 17 Feb 2024 16:12:15 +0100

On Sat, 17 Feb 2024 at 09:01, Michael Bondarenko
<[email-masked]> wrote:
> 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:
...
> But calculation is present for hour, and minutes and seconds (90061 sec is 1 day 1 hour 1 minute 1 second):

No, intervals have seconds, days and months. This is because not all
days have 24 hours, due to DST they can have 23 or 25, or even more
extreme values if some country decides to change its time zone
definition. And not all months have 30 days, so 90061 is 0 months, 0
days, 25 hours, 1 minute, 1 second ( IIRC leap second are not handled
).

It is done that way so when you add one day across a dst jump you get
the same hour on the next day, and when you add one month you get the
same day in the next month independent of how many days the month has.
This is great for things like "schedule a meeting one month and one
week from now", but it bites you sometimes, like when you need a
duration to bill for a long event like a phone call, where I always
end up extracting epoch and substracting them.

Francisco Olarte.