Re: computing dT from an interval

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

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.

regards, tom lane