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