Truncation bug when retrieving timestamps with fractional seconds (PG 7.3) - Mailing list pgsql-jdbc

Hi,

I believe I've discovered a bug in the JDBC implementation that ships
with PostgreSQL 7.3:  When loading a timestamp, the fractional seconds
may be truncated.  Specifically, if there are more than three
fractional digits, they are truncated to three.  If there are three or
two, they are truncated by one.  If there is one, it is left alone.


The attached class (Postgresql73TimestampBug) demonstrates this
problem.  (See the comment at the top of the class for table
requirements.)  On my machine, the output is:

1) DIFFERENT:
  Timestamp as stored: 2003-04-05 06:55:44.012345678
  Timestamp as loaded: 2003-04-05 06:55:44.012
2) DIFFERENT:
  Timestamp as stored: 2003-04-05 06:55:44.123456789
  Timestamp as loaded: 2003-04-05 06:55:44.123
3) DIFFERENT:
  Timestamp as stored: 2003-04-05 06:55:44.123
  Timestamp as loaded: 2003-04-05 06:55:44.12
4) DIFFERENT:
  Timestamp as stored: 2003-04-05 06:55:44.12
  Timestamp as loaded: 2003-04-05 06:55:44.1
5) SAME:
  Timestamp as stored: 2003-04-05 06:55:44.1
  Timestamp as loaded: 2003-04-05 06:55:44.1
6) SAME:
  Timestamp as stored: 2003-04-05 06:55:44.0
  Timestamp as loaded: 2003-04-05 06:55:44.0

Examining contents of the table into which these timestamps were stored
(using psql) reveals that the truncation is happening during loading:

  id |             ts
----+----------------------------
   1 | 2003-04-05 06:55:44.012345
   2 | 2003-04-05 06:55:44.123456
   3 | 2003-04-05 06:55:44.123
   4 | 2003-04-05 06:55:44.12
   5 | 2003-04-05 06:55:44.10
   6 | 2003-04-05 06:55:44

I am running OS X 10.2.3 with JDK 1.3.1.  I am using Marc Liyanage's
prebuilt PostgreSQL 7.3 distribution.  I have tried the JDBC driver
that is included with that distro as well as pg73jdbc2.jar and
pg73jdbc2ee.jar from the PostgreSQL JDBC download site.  All behave the
same.

Please let me know if I can provide any additional information.

Thanks,
Rhett

--
Rhett Sutphin
Research Assistant (Software)
Coordinated Laboratory for Computational Genomics
   and the Center for Macular Degeneration
University of Iowa - Iowa City, IA 52242 - USA
4111 MEBRF - email: rhett-sutphin@uiowa.edu