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.