To: Tom Lane <tgl@sss.pgh.pa.us>, Thomas Munro <thomas.munro@gmail.com>
Cc: pgsql-bugs@postgresql.org
References: <42b73d2d-da12-ba9f-570a-420e0cce19d9@phystech.edu>
 <CA+hUKGKgHUbFNKRhvodn4mVU-oqje+HA7jTg_XrWRo4UGhFoqg@mail.gmail.com>
 <18389.1575241151@sss.pgh.pa.us>
From: Petr Fedorov <petr.fedorov@phystech.edu>
Autocrypt: addr=petr.fedorov@phystech.edu; keydata=
 mQENBFemOXIBCADb1esaXYbV2DrN02akc5wgmGKtZVUdCbEma4OOAjCXz0uyk9mLpUUJwLtw
 Ad5Ur0V3HCxxUzeSSBJZdQzYMlhd8rWYgyCuEXTzMmnUelNd3TobEGlB6oLsBxccBw8zSgVd
 0AfTUTGT8n5zfVOChmt5zLFUzGNjBHMocITXK7Wr4kSEA8D9Yp9C7+iPwT3Ni5eHzhmzXfEH
 R1zT9kagsLQO5wFt3EOzFJNOPKH8W7SqA5zWndgxH0QlZTmr6flEhsLc0WxfHEB8dluWSBQc
 w5WDXaM+l778IqYshLVDUcRxcPE6pzj27+2ugovidKfc7TbQhnQKG9by2czU6OV+jiYnABEB
 AAG0KFBldHIgRmVkb3JvdiA8cGV0ci5mZWRvcm92QHBoeXN0ZWNoLmVkdT6JATgEEwECACIF
 AlemOXICGwMGCwkIBwMCBhUIAgkKCwQWAgMBAh4BAheAAAoJECPVmdiEygE+5asIAISOfBgf
 oRgUVSRf2l1ZUoxJCamSBxtt6vTGdxJjotJlwF+Iyircpcx55rDAs1H6aq7zfU/TRBTLTVod
 PdStghINkkWtomZ1i8XnXnvIwdYvP4l3rfwnJ7fWJG9H0qbKMAWzDYaEF1r6Wcl6QeH4hXrk
 T/CXQPY98JTl8kdYnsKWoGkrIbwu14HlaOO7CWuI1iJVkeDYt3H9txT0oqnGukH/dyD8cIC0
 zNdGIsmCWOKb4CduVRXKNwaZZ0061O4BVuz+Tf+McaYFI6KuIdi05JEVEk07zkpMZSKL2bTW
 B/CcdPpszesJ7tlsBHcrbGg4B4U/OZh7/0mLR5ySFuv1vpC5AQ0EV6Y5cgEIAKkkmIILFsu0
 Qg14VvYhhq85pXKRE4byXdTgfUAheduZRB1RNTgo55/xQXapEHvFsqyCZJVS7sY3p0EKLSk1
 tStuySxgumEEdXeXv99W6p34zpyh7sys76zW7RATjwZnlHYlQ0qSomZ4wj3DSv4e1x+9FitG
 NlqAvSN6XyHMLczVTHyaPfeedLh9Ghuvy9OeTrCeZ0mmDSiESaW8VojaIgpbGKQuBYsY61NH
 Fkgmb6jU+hDm0vV4l7Bn/0l756nt9nnt8VV7NNgvV63BVfyIHrJvm0yGQhXF86yWB7+XZx/F
 0KLlZSoGpzFJfk6qSgLSnqFfI2OpXY7Vo49SsSyx7+UAEQEAAYkBHwQYAQIACQUCV6Y5cgIb
 DAAKCRAj1ZnYhMoBPueACAC9EHwB3a9UBHI8haKw3kGZbqOBthz1SLHCgFsw/jxGbWKC2cCa
 9PL6ArCBvQelVOgkxfxN4gmQTFcwTD8AiTU3IffJxz8nFv/NRJptvUsPPhehmYGd2zp6D4O8
 Gc/2JUgGcm0eNhhxqZAlSlzTAtHM23I4/yTiHgyiyawq7yQknLedgVkwsH+HTsHWYR0X4kg4
 eErbPNj/B3+ZUAg9A6tRQOg9+B903gKsgtQacaTRSVA74kzaLNa4yevq97O0fBSSePCvX+Ti
 z7xx67iuIxa+yuZrzW2+itA6E5wLgfacBFkP8vZn4YrvA1MX0BP3iRCXxlNMr/D/rXgMKAya 6D03
Subject: Re: Since '2001-09-09 01:46:40'::timestamp microseconds are lost when
 extracting epoch
Message-ID: <1c2262e2-6812-7baa-cbdd-63039db12952@phystech.edu>
Date: Mon, 2 Dec 2019 14:08:43 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.1.1
MIME-Version: 1.0
In-Reply-To: <18389.1575241151@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Content-Language: en-US
Precedence: bulk

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

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

int main() {
=C2=A0double from =3D 1000000000.000020;
=C2=A0std::cout << std::setprecision(56) << from=C2=A0 << " (" << std::he=
xfloat <<
from << ") " << std::endl;
=C2=A0for(auto i =3D 0; i < 15; ++i) {
=C2=A0=C2=A0 double to =3D std::nextafter( from, std::numeric_limits<doub=
le>::max());
=C2=A0=C2=A0 std::cout << std::defaultfloat << to << std::hexfloat << " (=
" << to
<< ") " << std::endl;
=C2=A0=C2=A0 from =3D to;
=C2=A0}
}

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.=C2=A0

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 =D0=BF=D0=B8=D1=88=D0=B5=D1=82:
> Thomas Munro <thomas.munro@gmail.com> writes:
>> On Sat, Nov 30, 2019 at 10:28 PM Petr Fedorov <petr.fedorov@phystech.e=
du> 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