Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when
 extracting epoch
To: Thomas Munro <thomas.munro@gmail.com>,
 Petr Fedorov <petr.fedorov@phystech.edu>
References: <42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu>
 <CA+hUKGKgHUbFNKRhvodn4mVU-oqje+HA7jTg_XrWRo4UGhFoqg@mail.gmail.com>
 <18389.1575241151@sss.pgh.pa.us>
 <1c2262e2-6812-7baa-cbdd-63039db12952@phystech.edu>
 <CA+hUKGK+K8NyS57tU841m1tSF6ha3W0udN2dw_Hbc=D_0OPbmA@mail.gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers <pgsql-hackers@postgresql.org>
From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Organization: 2ndQuadrant
Message-ID: <ee683994-8f7c-aded-be43-543e66e68b94@2ndquadrant.com>
Date: Mon, 25 May 2020 15:28:54 +0200
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:52.0)
 Gecko/20100101 Thunderbird/52.9.1
MIME-Version: 1.0
In-Reply-To: 
 <CA+hUKGK+K8NyS57tU841m1tSF6ha3W0udN2dw_Hbc=D_0OPbmA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 2019-12-02 23:52, Thomas Munro wrote:
>> I'm not an expert in floating point math but hopefully it means that no
>> type change is required - double precision can handle it.
> Me neither, but the SQL standard requires us to use an exact numeric
> type, so it's wrong on that level by definition.

I looked into this (changing the return types of date_part()/extract() 
from float8 to numeric).

One problem (other than perhaps performance, tbd.) is that this would no 
longer allow processing infinite timestamps, since numeric does not 
support infinity.  It could be argued that running extract() on infinite 
timestamps isn't very useful, but it's something to consider explicitly.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services