Re: computing dT from an interval

On Sat, 2021-02-20 at 11:20 -0500, Tom Lane wrote:
> "Michael J. Baars" <mjbaars1977(dot)pgsql-hackers(at)cyberfiber(dot)eu> writes:
> > Can someone please tell me which of these two queries gives the correct result and which one the incorrect?
> > // 2.922 (&)
> > with A1 as ( select make_interval (0, 0, 0, 0, 0, 0, ( extract ( epoch from interval '8 years' ) / 1000 ) ) as "00" ) select ( extract ( hours from "00" ) +
> > extract ( minutes from "00" ) / 60 + extract ( seconds from "00" ) / 3600 ) / 24 as dT from A1;
> > // 2.88 (X)
> > with A1 as ( select interval '8 years' / 1000 as "00" ) select extract ( days from "00" ) + extract ( hours from "00" ) / 24 + extract ( minutes from "00" )
> > /
> > 1440 + extract ( seconds from "00" ) / 86400 as dT from A1;
>
> They'e both "incorrect", for some value of "incorrect". Quantities like
> years, days, and seconds don't interconvert freely, which is why the
> interval datatype tries to keep them separate.
>
> In the first case, the main approximation is introduced when you do
>
> select extract ( epoch from interval '8 years' );
> date_part
> -----------
> 252460800
> (1 row)
>
> If you do the math, you'll soon see that that corresponds to assuming
> 365.25 days (of 86400 seconds each) per year. So that's already wrong;
> no year contains fractional days.

I don't see the problem in this, we have 6 years of 365 days and 2 years of 366 days. Using this dt, we can compute a set of equidistant time stamps with their
corresponding values. Only the first and last row need to be on certain specific points in time, n * dt for certain n does not need to end up on january 1st for
each year in the interval.

Actually I only need to know for the moment, if dt spans more or less than one week and more or less than one day :)

>
> In the second case, the trouble starts with
>
> select interval '8 years' / 1000;
> ?column?
> -----------------
> 2 days 21:07:12
> (1 row)
>
> Internally, '8 years' is really 96 months, but to divide by 1000 we
> have to down-convert that into the lesser units of days and seconds.
> The approximation that's used for that is that months have 30 days,
> so we initially get 2.88 days, and then the 0.88 days part is
> converted to 76032 seconds.
>
> So yeah, you can poke a lot of holes in these choices, but different
> choices would just be differently inconsistent. The Gregorian calendar
> is not very rational.
>
> Personally I stay away from applying interval multiplication/division
> to anything except intervals expressed in seconds. As soon as you
> get into the larger units, you're forced to make unsupportable
> assumptions.

So how do you compute the number of seconds in 8 years?

>

I really think the first one does give the correct answer. The only thing is that the second one, the most trivial one of the two, does not give the same answer
as the first. They should have returned exactly the same number if you ask me.

> regards, tom lane
>

Regards,
Mischa Baars.