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