PostgreSQL JDBC - ResultSet#xxxTimestamp for DATE column unexpected behavior

Author

ResultSet#xxxTimestamp for DATE column unexpected behavior

dircha

2005-09-21, 8:24 pm

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#getTimesta
mp 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#loadC
alendar 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Oliver Jowett

2005-09-21, 8:24 pm

dircha wrote:

> pgsql-jdbc version: postgres80-312-jdbc2.jar

Please try the current CVS driver -- I made a number of fixes to
timestamp handling in CVS recently.

-O

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

dircha

2005-09-22, 8:24 pm

On Thu, 22 Sep 2005 09:50:58 +1200, "Oliver Jowett"
<oliver@opencloud.com> said:

> dircha wrote:
>
>
> Please try the current CVS driver -- I made a number of fixes to
> timestamp handling in CVS recently.

I built the latest pgsql-jdbc from cvs HEAD and ran the previously
described tests again.

Both issues I mentioned appear to have been fixed; the behavior is
as expected.

Great.

Is there any word yet on which official build these changes might be
expected to be present in, in terms of the labeling used on the pgsql-
jdbc downloads page? Will there be another 8.0-xxx.jdbc3.jar, or would
these changes go into a forthcoming stable 8.1-xxx.jdbc3.jar?

For reference, the updated results of the previously described test
are below.

Thanks!

--dircha

-------BEGIN TEST DATE AS TIMESTAMP-----------
- Creating test.

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

Retrieving date from test: 2000-01-01

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

Updating test with retrieved timestamp

Retrieving date from test: 2000-01-01

-------END TEST DATE AS TIMESTAMP------------------BEGIN TEST DATE AS
TIMESTAMP-----------
- Creating test.

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

Retrieving date from test: 3000-01-01

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

Updating test with retrieved timestamp

Retrieving date from test: 3000-01-01

-------END TEST DATE AS TIMESTAMP----------- DONE

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Kris Jurka

2005-09-29, 8:24 pm

dircha wrote:
> Both issues I mentioned appear to have been fixed; the behavior is
> as expected.
>
> Great.
>
> Is there any word yet on which official build these changes might be
> expected to be present in, in terms of the labeling used on the pgsql-
> jdbc downloads page? Will there be another 8.0-xxx.jdbc3.jar, or would
> these changes go into a forthcoming stable 8.1-xxx.jdbc3.jar?
>

The timestamp changes will not make their way into the 8.0 series
because they are too big. The 8.1 development series will become the
official stable release when the 8.1 server version officially releases.
I hope to put up a new 8.1 development release next week.

Kris Jurka

Sponsored Links