Re: value returned by EXTRACT, date_part

On Fri, Aug 28, 2020 at 10:43 AM John Lumby <johnlumby(at)hotmail(dot)com> 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.