Thread: Postgres (psql ?) rounds all odd second values to even seconds fo r timestamp(0) data type
Postgres (psql ?) rounds all odd second values to even seconds fo r timestamp(0) data type
From
Csaba Nagy
Date:
Looks like postgres will "round" all odd second values to even seconds for
timestamp(0).
Is this a known behaviour, or it's a bug ? The data type docs say nothing
about this, searching the mail archive/Google returned no relevant result.
BTW, I'm using the 7.3b5 version.
cnagy=> select '1999-01-28 18:17:15'::timestamp(0);
timestamp
---------------------
1999-01-28 18:17:16
(1 row)
cnagy=> select '1999-01-28 00:00:01'::timestamp(0);
timestamp
---------------------
1999-01-28 00:00:02
(1 row)
The same happens for ANY date having the seconds an odd number.
Cheers,
Csaba.
Re: Postgres (psql ?) rounds all odd second values to even seconds fo r timestamp(0) data type
From
Tom Lane
Date:
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