Re: [BUGS] 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(at)tg-embedded(dot)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(at)tg-embedded(dot)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

Nice test case. I did some research and realized that there is an
incorrect use of rint() in the code. The problem is that you can't
rint() if you can't overflow to the next units, and you can't rint() if
you might need to print the lesser units. In this case, we hit both of
those problems, so the fix is to remove rint() in the two places that
have it.

Notice before how the overflow to a full second happens:

16-Feb-2007 22:03:23. 999 999427
16-Feb-2007 22:03:23. 999 999461
16-Feb-2007 22:03:23. 999 999495
16-Feb-2007 22:03:23. 1000 999529
16-Feb-2007 22:03:23. 1000 999563
16-Feb-2007 22:03:23. 1000 999597
16-Feb-2007 22:03:23. 1000 999631
16-Feb-2007 22:03:23. 1000 999665
16-Feb-2007 22:03:23. 1000 999699
16-Feb-2007 22:03:23. 1000 999733
16-Feb-2007 22:03:23. 1000 999767
16-Feb-2007 22:03:23. 1000 999801
16-Feb-2007 22:03:23. 1000 999835
16-Feb-2007 22:03:23. 1000 999869
16-Feb-2007 22:03:23. 1000 999903
16-Feb-2007 22:03:23. 1000 999937
16-Feb-2007 22:03:23. 1000 999971
16-Feb-2007 22:03:24. 000 000006
16-Feb-2007 22:03:24. 000 000039
16-Feb-2007 22:03:24. 000 000072

and without rint():

16-Feb-2007 21:55:04. 999 999904
16-Feb-2007 21:55:04. 999 999939
16-Feb-2007 21:55:04. 999 999973
16-Feb-2007 21:55:05. 000 000007
16-Feb-2007 21:55:05. 000 000040
16-Feb-2007 21:55:05. 000 000074

Patch attached and applied, with comment added about rint() removal.

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

Anthony Taylor wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2996
> Logged by: Anthony Taylor
> Email address: tony(at)tg-embedded(dot)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;
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +