On 2020-08-28 15:44, David G. Johnston wrote:
> On Fri, Aug 28, 2020 at 10:43 AM John Lumby <johnlumby@hotmail.com
> <mailto:johnlumby@hotmail.com>> wrote:
>
>
> SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
> Result: 40
>
> rounded down from 40.0006944444444444445
>
>
> SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16
> 20:38:40.12-08');
> Result: 982384720.12
>
> un-rounded
>
>
> epoch is defined as "number of seconds since" and seconds is defined
> to include fractional parts - so one can argue that the indirection is
> bad but it is documented.
>
Thanks David, I had missed the mention of fractional part on seconds
and milliseconds.
But I think you had to dance pretty vigorously to make that defence
stand up.
In the description of epoch, the word "seconds" is not in "code" font
so one would presume it is the ordinary meaning, not a reference to
another field.
>
> The ones that return fractions are documented as such (I think just
> seconds, and its relative epoch).
>
I think it could be made clearer , in particular :
. where it is stated that the returned type is double precision,
state that values are rounded down unless explicitly described as
including a fractional part
. for "epoch" add the statement that this includes a fractional part.
>
> David J.
But also (and probably not easily changed now) it would make EXTRACT
more generally useful if it included a fractional part for every field
type where meaningful.
Is there any reason why it should not?
For example, after trying a few ideas I *think* postgresql (v12)
does not offer any way to get an exact, double-precision rendition of
the interval in the above example
What I want is something like
SELECT EXTRACT(<anyunit> FROM INTERVAL '40 days 1 minute');
returns a double-precision type with a value representing a stretch of
time exactly equal to 40.0006944444444444445 days or 57601.0 minutes
EXTRACT(DAY FROM INTERVAL '40 days 1 minute') doesn't as we have seen
date_trunc('minute' , INTERVAL '40 days 1 minute') doesn't
But I suppose it depends on one's interpretation of what a "subfield" of
an interval is.
