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