Re: value returned by EXTRACT, date_part

Thanks Tom

On 2020-08-28 21:39, Tom Lane wrote:
>
> 4.6.3 "Intervals" lays down basically the same sorts of rules for
> intervals: they are made of component fields and only the seconds
> field can have a fractional part.

What is not clear to me is how the "components"  (aka "subfields") of an
interval are defined.

e.g.

SELECT EXTRACT(days FROM INTERVAL '1 year 35 days 1 minute');
 date_part
-----------
        35

ok,     it takes the interval modulo months (the next higher unit than
the one I requested) and then rounds that down.

But

SELECT EXTRACT(days FROM INTERVAL '400 days 1 minute');
 date_part
-----------
       400

oh!    no it doesn't ...

>
> PG does offer a nonstandard EPOCH "field" in EXTRACT, which tries
> to convert the timestamp or interval as a whole to some number of
> seconds. Possibly you could make use of that, perhaps after first
> applying date_trunc, to get what you're after. The whole enterprise
> is pretty shaky though; for example you cannot convert months to days
> or vice versa without making fundamentally-indefensible assumptions.

Yes!        That is exactly what I am looking for.    Actually for
simplicity I think I don't need date_trunc;

for this particular case of wanting to find the size of an interval,

it is as simple as always requesting its epoch and working in
double-precision seconds.

Thanks!

>
> regards, tom lane
> .