value returned by EXTRACT, date_part

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

value returned by EXTRACT, date_part

johnlumby
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



Reply | Threaded
Open this post in threaded view
|

Re: value returned by EXTRACT, date_part

David G Johnston
On Fri, Aug 28, 2020 at 10:43 AM John Lumby <[hidden email]> wrote:
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

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.

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?

The ones that return fractions are documented as such (I think just seconds, and its relative epoch).

It does it that way because of the nature of writing overloaded functions.

David J.
Reply | Threaded
Open this post in threaded view
|

Re: value returned by EXTRACT, date_part

johnlumby

On 2020-08-28 15:44, David G. Johnston wrote:

> On Fri, Aug 28, 2020 at 10:43 AM John Lumby <[hidden email]
> <mailto:[hidden email]>> 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.



Reply | Threaded
Open this post in threaded view
|

Re: value returned by EXTRACT, date_part

Tom Lane-2
John Lumby <[hidden email]> <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: value returned by EXTRACT, date_part

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


Reply | Threaded
Open this post in threaded view
|

Re: value returned by EXTRACT, date_part

Tom Lane-2
John Lumby <[hidden email]> <[hidden email]> 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