Thread: BUG #2996: to_char( timestamp, 'DD-Mon-YYYY HH24:MI:SS.MS' ) reports .1000 ms

The following bug has been logged online:

Bug reference:      2996
Logged by:          Anthony Taylor
Email address:      tony@tg-embedded.com
PostgreSQL version: 8.1.8
Operating system:   Linux kernel 2.6.11 (based on Gentoo)
Description:        to_char( timestamp, 'DD-Mon-YYYY HH24:MI:SS.MS' )
reports .1000 ms
Details:

When using the "to_char" function to output timestamps, some timestamps
report .1000 milliseconds.

Specifically,

select to_char( time, 'DD-Mon-YYYY HH24:MI:SS.MS' ) from test_time;

Reports:

 12-Feb-2007 18:16:34.999
 12-Feb-2007 18:16:34.1000
 12-Feb-2007 18:16:35.000

I believe the 34.1000 should either be 34.999 or 35.000.

According to the documentation (table 9-21, Template Patterns for Date/Time
Formatting):

MS    millisecond (000-999)

Here's a nice little test script:

-- --------------------------------

CREATE TABLE test_time ( time TIMESTAMP );

CREATE OR REPLACE FUNCTION timetest( )
    RETURNS VOID
    AS $$
BEGIN
    FOR i IN 0..100000 LOOP
        INSERT INTO test_time VALUES ( timeofday()::timestamp );
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT timetest();

select to_char( time, 'DD-Mon-YYYY HH24:MI:SS.MS' ) from test_time;

"Anthony Taylor" <tony@tg-embedded.com> writes:
> When using the "to_char" function to output timestamps, some timestamps
> report .1000 milliseconds.

Confirmed here: using your test case, successive timestamps look like

 14-Feb-2007 02:44:04.998
 14-Feb-2007 02:44:04.998
 14-Feb-2007 02:44:04.998
 14-Feb-2007 02:44:04.998
 14-Feb-2007 02:44:04.999
 14-Feb-2007 02:44:04.999
 14-Feb-2007 02:44:04.999
 14-Feb-2007 02:44:04.999
 14-Feb-2007 02:44:04.999
 14-Feb-2007 02:44:04.999
 14-Feb-2007 02:44:04.1000
 14-Feb-2007 02:44:04.1000
 14-Feb-2007 02:44:04.1000
 14-Feb-2007 02:44:05.000
 14-Feb-2007 02:44:05.000
 14-Feb-2007 02:44:05.000
 14-Feb-2007 02:44:05.001
 14-Feb-2007 02:44:05.001
 14-Feb-2007 02:44:05.001
 14-Feb-2007 02:44:05.001
 14-Feb-2007 02:44:05.001

Not having looked at the code, my bet is that this occurs only without
--enable-integer-timestamps; is your installation compiled with that?

It would be interesting to check what happens at an hour or day
boundary; I suspect the roundoff problem may extend to higher units.
We've seen related bugs before :-(

            regards, tom lane