Re: [BUGS] BUG #3761: Problems with to_char(timestamp) and milliseconds

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));