ResultSet#xxxTimestamp for DATE column unexpected behavior - Mailing list pgsql-jdbc

PostgreSQL version: 7.4.7
pgsql-jdbc version: postgres80-312-jdbc2.jar
java version: 1.4.2_07-b05
opearting system: debian sarge

*system timezone: UTC-0600.

I believe there to be one defect here, and potentially a second.

First, the return value of ResultSet#getTimestamp is unexpected for a
DATE column.

Second, setting the retrieved timestamp as the value for the DATE column
assigns a value one day after the initial date value, for some initial
values.

Note, it turned out to be incorrect for other reasons that #getTimestamp
and #setTimestamp were being used on the DATE column in our code in the
first place. It was a Hibernate configuration issue.

But apart from that, I believe the driver is still behaving incorrectly
- or at least unexpectedly - here.

The issues are illustrated by the following code snippet:

    Connection c = [...]
    Statement st = c.createStatement();

    System.out.println("- Creating test.");
    st.execute("create table test (column1 date)");
    System.out.println();

    System.out.println("- Inserting '3000-1-1' into test.");
    st.execute("insert into test (column1) values('3000-1-1')");
    System.out.println();

    System.out.println("Retrieving date from test:");
    ResultSet rs = st.executeQuery("select column1 from test");
    rs.next();
    java.sql.Date sd = rs.getDate(1);
    System.out.println(sd);
    System.out.println();

    System.out.println("Retrieving timestamp from test:");
    rs = st.executeQuery("select column1 from test");
    rs.next();
    java.sql.Timestamp t = rs.getTimestamp(1);
    System.out.println(t);
    System.out.println();

    System.out.println("Updating test with retrieved timestamp");
    PreparedStatement pst = c.prepareStatement("update test set
    column1=?");
    pst.setTimestamp(1, t);
    pst.executeUpdate();
    System.out.println();

    System.out.println("Retrieving date from test:");
    rs = st.executeQuery("select column1 from test");
    rs.next();
    sd = rs.getDate(1);
    System.out.println(sd);
    System.out.println();

Executing this snippet produces the following output:
- Creating test.

- Inserting '3000-1-1' into test.

Retrieving date from test:
3000-01-01

Retrieving timestamp from test:
3000-01-01 18:00:00.0

Updating test with retrieved timestamp

Retrieving date from test:
3000-01-02


1.
Shouldn't the retrieved timestamp be 2999-12-31 18:00:00.0 instead of
3000-01-01 18:00:00.0?

This appears to be caused by
org.postgresql.jdbc.TimestampUtils#loadCalendar initializing the
calendar with "new java.util.Date(0)". Then, since the incoming column
value is only "3000-01-01", no time portion is parsed, so the initial
"18:00:00" is exposed.

2.
Updating the column with the retrieved Timestamp results in the date
being incremented in what I assume is the conversion from UTC-0600 to
UTC.

Note that this appears not to happen if the initial date being used is,
say, 2000-1-1 instead of 3000-1-1 as per the following output:

- Creating test.

- Inserting '2000-1-1' into test.

Retrieving date from test:
2000-01-01

Retrieving timestamp from test:
2000-01-01 18:00:00.0

Updating test with retrieved timestamp

Retrieving date from test:
2000-01-01

If I set a breakpoint on the line "pst.executeUpdate", and examine
pst.ps.preparedParameters.parameterValues, the prepared values are
3000-01-01 18:00:00.000000-0600 and 2000-01-01 18:00:00.000000-0600
respectively. Based on this, I can't see why the behavior would differ
as it does.

Is this second issue somehow related to limitations of the system date
and timezone facilities?

Thanks!

--dircha