Thread: Re: Postgres (psql ?) rounds all odd second values to e

Hi Tom,

I've looked at the code you mentioned, did some experimenting.
I still don't understand why the negative case ? What dates would be in that
range ?
In any case, the negative number handling seems not doing the same thing as
the positive one.
The "rint" implementation on my test box has some inconsistentcies too:

rint(100) = 100
rint(100.49) = 100
rint(100.5) = 100
rint(100.51) = 101
rint(100.99) = 101

rint(101) = 101
rint(101.49) = 101
rint(101.5) = 102
rint(101.51) = 102
rint(101.99) = 102

rint(-100) = -100
rint(-100.49) = -100
rint(-100.5) = -100
rint(-100.51) = -101
rint(-100.99) = -101

rint(-101) = -101
rint(-101.49) = -101
rint(-101.5) = -102
rint(-101.51) = -102
rint(-101.99) = -102

Looks like rounding is not working always consistently for "xxx.5", and
sometimes rounds up, sometimes down. In any case it will NOT truncate, as
the int64 implementation does.
If I would understand the meaning of the negative case, I would try to find
a consistent solution here.

TIA,
Csaba.


-----Ursprüngliche Nachricht-----
Von: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Gesendet: Mittwoch, 8. Januar 2003 18:11
An: Csaba Nagy
Cc: pgsql-general@postgresql.org; Thomas Lockhart
Betreff: Re: [GENERAL] Postgres (psql ?) rounds all odd second values to
even seconds fo r timestamp(0) data type


Csaba Nagy <nagy@domeus.de> writes:
> Looks like postgres will "round" all odd second values to even seconds for
> timestamp(0).

> cnagy=> select '1999-01-28 18:17:15'::timestamp(0);
>       timestamp
> ---------------------
>  1999-01-28 18:17:16
> (1 row)

Hmm.  I see it too --- but only for dates preceding 2000.

regression=# select '1999-01-28 18:17:15'::timestamp(0);
      timestamp
---------------------
 1999-01-28 18:17:16
(1 row)

regression=# select '2002-01-28 18:17:15'::timestamp(0);
      timestamp
---------------------
 2002-01-28 18:17:15
(1 row)

It looks to me like the cause is an ill-chosen rounding method in
AdjustTimestampForTypmod:

        /* we have different truncation behavior depending on sign */
        if (*time >= 0)
        {
            *time = (rint(((double) *time) * TimestampScales[typmod])
                     / TimestampScales[typmod]);
        }
        else
        {
            /*
             * Scale and truncate first, then add to help the rounding
             * behavior
             */
            *time = (rint((((double) *time) * TimestampScales[typmod]) +
TimestampOffsets[typmod])
                     / TimestampScales[typmod]);
        }

This presents rint() with a value having a fraction of exactly 0.5,
which (on most machines) will cause it to round to nearest even.

It seems to me that we could make the negative-time case read

            *time = - (rint(-((double) *time) * TimestampScales[typmod])
                     / TimestampScales[typmod]);

or even just eliminate the special case entirely; I know of no reason
not to trust rint() for negative values.  That would make it look more
like the 7.2 implementation of this routine.

Thomas, any comments here?

            regards, tom lane

Csaba Nagy <nagy@domeus.de> writes:
> I've looked at the code you mentioned, did some experimenting.
> I still don't understand why the negative case ? What dates would be in that
> range ?

IIRC, our internal representation of timestamps puts time 0 at midnight GMT
1/1/2000.  Times before that will be negative.

> In any case, the negative number handling seems not doing the same thing as
> the positive one.

That's what I fixed in the patch I applied on 8 Jan.

> The "rint" implementation on my test box has some inconsistentcies too:

Actually, that behavior of "rounding to nearest even" at an xxx.5
boundary is what is specified by the IEEE float-arithmetic standard.
The numerical-analysis justification for the spec doesn't seem to have
much applicability to datetime calculations, perhaps, but that's what
we're going to get on a lot of platforms.

            regards, tom lane