Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when extracting epoch

It appears that extract epoch returns double precision, not float8.  And
the program below seems to be demonstrating that there are enough
'floating-point numbers' as defined by  IEEE-754 to represent
1000000000.000021 precisely enough:

#include <cmath>
#include <iostream>
#include <iomanip>
#include <limits>

int main() {
 double from = 1000000000.000020;
 std::cout << std::setprecision(56) << from  << " (" << std::hexfloat <<
from << ") " << std::endl;
 for(auto i = 0; i < 15; ++i) {
   double to = std::nextafter( from, std::numeric_limits<double>::max());
   std::cout << std::defaultfloat << to << std::hexfloat << " (" << to
<< ") " << std::endl;
   from = to;
 }
}

Outputs:

1000000000.00002002716064453125 (0x1.dcd65000000a8p+29)
1000000000.00002014636993408203125 (0x1.dcd65000000a9p+29)
1000000000.0000202655792236328125 (0x1.dcd65000000aap+29)
1000000000.00002038478851318359375 (0x1.dcd65000000abp+29)
1000000000.000020503997802734375 (0x1.dcd65000000acp+29)
1000000000.00002062320709228515625 (0x1.dcd65000000adp+29)
1000000000.0000207424163818359375 (0x1.dcd65000000aep+29)
1000000000.00002086162567138671875 (0x1.dcd65000000afp+29)
1000000000.0000209808349609375 (0x1.dcd65000000bp+29)
1000000000.00002110004425048828125 (0x1.dcd65000000b1p+29)
1000000000.0000212192535400390625 (0x1.dcd65000000b2p+29)
1000000000.00002133846282958984375 (0x1.dcd65000000b3p+29)
1000000000.000021457672119140625 (0x1.dcd65000000b4p+29)
1000000000.00002157688140869140625 (0x1.dcd65000000b5p+29)
1000000000.0000216960906982421875 (0x1.dcd65000000b6p+29)
1000000000.00002181529998779296875 (0x1.dcd65000000b7p+29)

I'm not an expert in floating point math but hopefully it means that no
type change is required - double precision can handle it. 

And since it works correctly on v12 for this particular date may be all
what is needed it to verify that it works for the other dates too! For
example what was changed in v12 (comparing to 11.6 I use) so extract
epoch works correctly?


02.12.2019 01:59, Tom Lane пишет:
> Thomas Munro <thomas.munro@gmail.com> writes:
>> On Sat, Nov 30, 2019 at 10:28 PM Petr Fedorov <petr.fedorov@phystech.edu> wrote:
>>> Obviously, it is due to the fact that extract epoch returns double
>>> precision which in turn has 15 decimal digits precision.
>> I guess this deviation from the SQL standard ("exact numeric") made
>> sense when PostgreSQL used double for timestamps, but would break a
>> lot of queries if we changed it.
> Hmmm ... well, now that you mention it, would it really break things
> if we made it return numeric?  There's an implicit cast to float8,
> so it seems like queries requiring that type would still work.
>
> There might be a performance-related argument against switching,
> perhaps.
>
> 			regards, tom lane