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

Francisco Olarte <folarte(at)peoplecall(dot)com> writes:
> On Sat, 17 Feb 2024 at 09:01, Michael Bondarenko
> <work(dot)michael(dot)2956(at)gmail(dot)com> 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.

Yeah. I think much of the confusion here comes from starting with
non-normalized interval input. Sure you can write "2011 year 12 month
48 hour 1005 min 2 sec 11 ms", but that's not how it's stored:

regression=# select interval '2011 year 12 month 48 hour 1005 min 2 sec 11 ms';
interval
-------------------------
2012 years 64:45:02.011
(1 row)

(Actually, what's stored is 2012*12 months, 0 days, and some number
of microseconds that I don't feel like working out. Conversion of
the microseconds to HH:MM:SS.SSS happens on output.)

Once you look at the normalized value, the results of extract()
are far less surprising.

Probably the right place to enlarge on this point is not in the
extract() section at all, but in 8.5.4. Interval Input. That does
mention the months/days/microseconds representation, but it doesn't
follow through by illustrating how other input is combined. Perhaps
we'd want to adopt something like the attached (this is separate from
the other patches I posted in the thread).

regards, tom lane