value returned by EXTRACT, date_part

From: John Lumby <johnlumby(at)hotmail(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: value returned by EXTRACT, date_part
Date: 2020-08-28 17:43:12
Message-ID: DM6PR06MB5562115198C798B1C06B32DBA3520@DM6PR06MB5562.namprd06.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message David G. Johnston 2020-08-28 19:44:06 Re: value returned by EXTRACT, date_part
Previous Message Mike Martin 2020-08-22 12:52:02 Stored procedure and returning clause