Thread: value returned by EXTRACT, date_part

I notice that although EXTRACT(field FROM source) returns a value of 
type double-precision,

for some field types,  it apparently rounds the value to an integer,   
(even though the exact value is not integral),

whereas for others it returns the exact value including any fractional 
part un-rounded. Examples can be seen in the book :


SELECT EXTRACT(DAY FROM INTERVAL '40 days 1 minute');
Result: 40

rounded down from 40.0.0006944444444444445


SELECT EXTRACT(EPOCH FROM TIMESTAMP WITH TIME ZONE '2001-02-16 
20:38:40.12-08');
Result: 982384720.12

un-rounded


This seems inconsistent to me - and undocumented as far as I can tell.

Is there any reason for this?    And can it be relied on in programs?


Cheers,   John

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.

John Lumby <johnlumby@hotmail.com> <DM6PR06MB5562A231945B39D33623849BA3520@DM6PR06MB5562.namprd06.prod.outlook.com>
writes:
> 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?

The SQL spec says otherwise.

Section 4.6.2 "Datetimes" in SQL:2011 defines timestamps as containing
the six fields year, month, day, hour, minute, second (dates contain
only the first three, times only the second three, but otherwise are
similar), plus possibly timezone_hour and timezone_minute.  All of
these except the seconds field are specified to be integers.
Furthermore, the EXTRACT function is specifically defined to return
one of these fields.

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.

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.

            regards, tom lane

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

John Lumby <johnlumby@hotmail.com> <DM6PR06MB55625C1A9319BC921F4FB4ACA3530@DM6PR06MB5562.namprd06.prod.outlook.com>
writes:
> What is not clear to me is how the "components" (aka "subfields") of an
> interval are defined.

The spec says that year,month,day,hour,minute,second are independent
fields.  Postgres doesn't implement it quite that way: we treat the
basic fields as being months, days, and (micro)seconds, folding the
other values into those for storage.  That's why "14 months" comes out
as "1 year 2 months", for example.

            regards, tom lane