Re: [BUGS] BUG #1927: incorrect timestamp returned

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Right. We allow leap seconds for any date/time. Are you saying we
> > should only allow them for certain dates/times?
>
> No, his point is the funny roundoff behavior.
>
> regression=# select timestamp '2005-09-23 23:59:59.999999';
> timestamp
> ----------------------------
> 2005-09-23 23:59:59.999999
> (1 row)
>
> regression=# select timestamp '2005-09-23 23:59:59.9999999';
> timestamp
> ------------------------
> 2005-09-23 23:59:60.00
> (1 row)
>
> regression=# select timestamp '2005-09-23 23:59:59.99999999';
> timestamp
> ---------------------
> 2005-09-24 00:00:00
> (1 row)

I did some research on this. The difference is caused by the place in
the code where the rounding happens. Here is the simple case. The
second line is the return value, "double", from timestamp_in():

test=> select timestamp '2005-09-23 23:59:59.999999';
timestamp
----------------------------
2005-09-23 23:59:59.999999
180835199.99999899

Here is one where the rounding happens after timestamp_in() returns:

test=> select timestamp '2005-09-23 23:59:59.9999999';
timestamp
------------------------
2005-09-23 23:59:60.00
180835199.99999991

and in this case the rounding happens inside timestamp_in():

test=> select timestamp '2005-09-23 23:59:59.99999999';
timestamp
---------------------
2005-09-24 00:00:00
180835200

Looks like "time" has a similar problem:

test=> select time '2005-09-23 23:59:59.99999999';
time
-------------------
23:59:59.99999999
(1 row)

test=> select time '2005-09-23 23:59:59.99999999999';
time
-------------
23:59:60.00
(1 row)

test=> select time '2005-09-23 23:59:59.999999999999';
time
----------
24:00:00
(1 row)

I have gone through the code and identified all the places that need
JROUND, basically places where we do complex calculations that include
fsec (fractional seconds). This only affects timestamp=double backends,
not timestamp=int64.

The patch fixes all the test cases above, and passes all regression
tests.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073