ResultSet#xxxTimestamp for DATE column unexpected behavior

From: "dircha" <dircha(at)dircha(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: ResultSet#xxxTimestamp for DATE column unexpected behavior
Date: 2005-09-21 20:21:18
Message-ID: 1127334078.8792.243450226@webmail.messagingengine.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2005-09-21 21:50:58 Re: ResultSet#xxxTimestamp for DATE column unexpected behavior
Previous Message Nicolaus.Bauman 2005-09-21 18:32:33 Re: Help !! Postgres on JDeveloper+ADF