=?UTF-8?Q?Erik_Nordstr=C3=B6m?= <erik.nordst...@gmail.com> writes: > Thanks for the insightful feedback. You are right, the patch does suffer > from overflow (and other possible issues when I think about it). Using > rint(), as you suggest, helps in my original example case, but there are > still cases when the output is not what you would expect. For instance, > converting the Unix time 14864803242.312311 gives back the timestamp > "2441-01-17 09:00:42.312312+01", even if using rint() (see below).
Hm, that particular case works for me using the patch I committed yesterday (which just added a rint() call to the existing code). I'm a bit skeptical of the version you propose here because it seems mighty prone to subtractive cancellation. You're basically computing x - int(x) which can't add any precision that wasn't there before. Looking at successive microsecond values in this range: regression=# select 14864803242.312310::float8 - 14864803242; ?column? ------------------- 0.312309265136719 (1 row) regression=# select 14864803242.312311::float8 - 14864803242; ?column? ------------------- 0.312311172485352 (1 row) regression=# select 14864803242.312312::float8 - 14864803242; ?column? ------------------- 0.312311172485352 (1 row) regression=# select 14864803242.312313::float8 - 14864803242; ?column? ------------------- 0.312313079833984 (1 row) Basically, 1 ULP in this range is more than 1 microsecond, so there are going to be places where you cannot get the right answer. Reformulating the computation will just shift the errors to nearby values. float8 simply doesn't have enough bits to represent this many microseconds since 1970 exactly, and the problem's only going to get worse the further out you look. I think we might be better advised to add to_timestamp(numeric) alongside to_timestamp(float8). There's plenty of precedent for that (e.g, exp(), ln()) so I would not expect problems with ambiguous function calls. It'd be slower though ... regards, tom lane -- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers