Jose Tomas Eterovic wrote:
>
> The following bug has been logged online:
>
> Bug reference: 3761
> Logged by: Jose Tomas Eterovic
> Email address: roleroz@gmail.com
> PostgreSQL version: 8.2.4-r1
> Operating system: Gentoo Linux
> Description: Problems with to_char(timestamp) and milliseconds
> Details:
>
> When you use to_char to show as a string a date (including milliseconds),
> there's the possibility that the number of milliseconds will be 1000 (not
> between 000-999 as the documentation says)
>
> if you have a table (called "testTable") with a single column of type
> "timestamp" (called "testTimestamp") with lots of data, and you run the
> query
>
> SELECT to_char(testTimestamp, 'YYYY-MM-DD HH24:MI:SS.MS') FROM testTable
>
> you'll eventually get a result like
> 2007-10-24 13:44:48.1000
> instead of
> 2007-10-24 13:44:49.000
This is fixed in 8.3 beta with this attached patch.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: formatting.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/utils/adt/formatting.c,v
retrieving revision 1.127
retrieving revision 1.128
diff -c -c -r1.127 -r1.128
*** formatting.c 17 Feb 2007 01:51:42 -0000 1.127
--- formatting.c 17 Feb 2007 03:11:32 -0000 1.128
***************
*** 1,7 ****
/* -----------------------------------------------------------------------
* formatting.c
*
! * $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.127 2007/02/17 01:51:42 momjian Exp $
*
*
* Portions Copyright (c) 1999-2007, PostgreSQL Global Development Group
--- 1,7 ----
/* -----------------------------------------------------------------------
* formatting.c
*
! * $PostgreSQL: pgsql/src/backend/utils/adt/formatting.c,v 1.128 2007/02/17 03:11:32 momjian Exp $
*
*
* Portions Copyright (c) 1999-2007, PostgreSQL Global Development Group
***************
*** 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));
