Re: 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