Re: [BUGS] BUG #2996: to_char( timestamp, 'DD-Mon-YYYY HH24:MI:SS.MS' ) reports .1000 ms - Mailing list pgsql-patches
From | Bruce Momjian |
---|---|
Subject | Re: [BUGS] BUG #2996: to_char( timestamp, 'DD-Mon-YYYY HH24:MI:SS.MS' ) reports .1000 ms |
Date | |
Msg-id | 200702170311.l1H3Bax26143@momjian.us Whole thread Raw |
List | pgsql-patches |
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@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; > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/backend/utils/adt/formatting.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v retrieving revision 1.127 diff -c -c -r1.127 formatting.c *** src/backend/utils/adt/formatting.c 17 Feb 2007 01:51:42 -0000 1.127 --- src/backend/utils/adt/formatting.c 17 Feb 2007 03:09:43 -0000 *************** *** 2000,2006 **** #ifdef HAVE_INT64_TIMESTAMP sprintf(inout, "%03d", (int) (tmtc->fsec / INT64CONST(1000))); #else ! sprintf(inout, "%03d", (int) rint(tmtc->fsec * 1000)); #endif if (S_THth(suf)) str_numth(p_inout, inout, S_TH_TYPE(suf)); --- 2000,2007 ---- #ifdef HAVE_INT64_TIMESTAMP sprintf(inout, "%03d", (int) (tmtc->fsec / INT64CONST(1000))); #else ! /* No rint() because we can't overflow and we might print US */ ! sprintf(inout, "%03d", (int) (tmtc->fsec * 1000)); #endif if (S_THth(suf)) str_numth(p_inout, inout, S_TH_TYPE(suf)); *************** *** 2041,2047 **** #ifdef HAVE_INT64_TIMESTAMP sprintf(inout, "%06d", (int) tmtc->fsec); #else ! sprintf(inout, "%06d", (int) rint(tmtc->fsec * 1000000)); #endif if (S_THth(suf)) str_numth(p_inout, inout, S_TH_TYPE(suf)); --- 2042,2049 ---- #ifdef HAVE_INT64_TIMESTAMP sprintf(inout, "%06d", (int) tmtc->fsec); #else ! /* don't use rint() because we can't overflow 1000 */ ! sprintf(inout, "%06d", (int) (tmtc->fsec * 1000000)); #endif if (S_THth(suf)) str_numth(p_inout, inout, S_TH_TYPE(suf));
pgsql-patches by date: