Rhett,
Thanks for the good test case. I have committed a fix for this bug.
thanks,
--Barry
Rhett Sutphin wrote:
> 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.
>
>
> ------------------------------------------------------------------------
>
> // package test.jdbc;
>
> import java.sql.*;
>
> //
> // This test requires a table in the database pointed to
> // by DB_URL with the following structure:
> // CREATE TABLE timestamp_bug (
> // id INTEGER,
> // ts TIMESTAMP
> // );
> //
> public final class Postgresql73TimestampBug {
> private static String TABLE_NAME = "timestamp_bug";
> private static String DB_URL = "jdbc:postgresql://localhost:5432/test";
> private static String DB_USER = "test";
> private static String DB_PASS = "test";
>
> public static void main(String[] args) throws Exception {
> // load driver
> Class.forName("org.postgresql.Driver");
> // open connection to postgres
> Connection jdbc;
> jdbc = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
>
> test(Timestamp.valueOf("2003-04-05 06:55:44.012345678"), jdbc, 1);
> test(Timestamp.valueOf("2003-04-05 06:55:44.123456789"), jdbc, 2);
> test(Timestamp.valueOf("2003-04-05 06:55:44.123"), jdbc, 3);
> test(Timestamp.valueOf("2003-04-05 06:55:44.12"), jdbc, 4);
> test(Timestamp.valueOf("2003-04-05 06:55:44.1"), jdbc, 5);
> test(Timestamp.valueOf("2003-04-05 06:55:44"), jdbc, 6);
> }
>
> public static void test(Timestamp toStore, Connection jdbc, int key) throws SQLException {
> PreparedStatement stmt;
> // delete test entry
> stmt = jdbc.prepareStatement("DELETE FROM " + TABLE_NAME + " WHERE id=?");
> stmt.setInt(1, key);
> stmt.executeUpdate();
>
> // insert test entry
> stmt = jdbc.prepareStatement("INSERT INTO " + TABLE_NAME + " (id, ts) VALUES (?, ?)");
> stmt.setInt(1, key);
> stmt.setTimestamp(2, toStore);
> stmt.executeUpdate();
>
> // select test entry to read back
> stmt = jdbc.prepareStatement("SELECT ts FROM " + TABLE_NAME + " WHERE id=?");
> stmt.setInt(1, key);
> ResultSet results = stmt.executeQuery();
> results.next();
>
> Timestamp loaded = results.getTimestamp("ts");
>
> if (toStore.equals(loaded))
> System.out.println(""+key+") SAME:");
> else
> System.out.println(""+key+") DIFFERENT:");
> System.out.println(" Timestamp as stored: " + toStore);
> System.out.println(" Timestamp as loaded: " + loaded);
> }
> }
>
>
> ------------------------------------------------------------------------
>
>
> 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
>
>
> ------------------------------------------------------------------------
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly