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

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. 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.