Re: value returned by EXTRACT, date_part

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.