Erik Nordström <erik.nordstrom@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