Re: 7.3 -> 8.0.4 migration timestamp problem

Eliézer Madeira de Campos wrote:

>  1) Timestamp before 1914.
>     When I execute the query by java statement no problem occurs, but when I execute the query
>     by java preparedstatement this is what happens:
>
>       PreparedStatement pst = con.prepareStatement("insert into teste values (?, ?, ?)");
>       pst.setObject(1, new Integer(1));
>       pst.setObject(2, "TESTE");
>       Calendar c = new GregorianCalendar(1913, 0, 1, 0, 0, 0);
>       Timestamp t = new Timestamp(c.getTimeInMillis());
>       pst.setTimestamp(3, t);
>       pst.executeUpdate();
>
>     The date stored in database is actually 1912-12-31 23:53:12.0 (however it should have stored 1913-01-01.
>
>     I have already debugged the Postgres-8.0 (build 313) driver and it seems to send the correct date to database.

I could not reproduce this problem using the attached testcase against
8.0.3 and the build 313 driver. I get the expected output:

> $ java -classpath .:postgresql-8.0-313.jdbc3.jar TestTimestamp6 'jdbc:postgresql:test?user=oliver'
> 1 => 1913-01-01 00:00:00.0 (literal '1913-01-01 00:00:00')

What are you doing differently? Perhaps the JVM and server timezone
settings are important, what are you using?

-O
import java.sql.*;
import java.util.*;
import java.text.*;

public class TestTimestamp6 {
    public static void main(String[] args) throws Exception {
        Class.forName("org.postgresql.Driver");

        Connection conn = DriverManager.getConnection(args[0]);
        Statement stmt = conn.createStatement();

        try {
            stmt.executeUpdate("DROP TABLE testtimestamp6");
        } catch (SQLException e) {}

        stmt.executeUpdate("CREATE TABLE testtimestamp6(index int4, datetime timestamp without time zone)");

        PreparedStatement pst = conn.prepareStatement("insert into testtimestamp6 values (?, ?)");
        pst.setObject(1, new Integer(1));
        Calendar c = new GregorianCalendar(1913, 0, 1, 0, 0, 0);
        Timestamp t = new Timestamp(c.getTimeInMillis());
        pst.setTimestamp(2, t);
        pst.executeUpdate();

        ResultSet rs = stmt.executeQuery("select * from testtimestamp6");
        while (rs.next()) {
            int index = rs.getInt(1);
            Timestamp ts = rs.getTimestamp(2);
            String ts_text = rs.getString(2);
            System.out.println(index + " => " + ts + " (literal '" + ts_text + "')");
        }
    }
}