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