Re: Bug in JDBC-Driver?

Kris,

Seems to me we would have the same issues wth Date without time zone, no?

Here's a couple of possible solutions.
Since timestamp without timezone is a postgresql extension, that falls
outside the specification of the interface

we could 1) create an org.postgresql.Statement.setTimeStampz()
2) create an org.postgresql.types.Timestampz, Datez

This would also solve the issue with interval, as currently the driver
doesn't handle interval (AFAIK)

Dave

Kris Jurka wrote:

>On Mon, 29 Nov 2004 Antje(dot)Stejskal(at)ppi(dot)de wrote:
>
>
>
>>I am facing a time problem with timestamp handling. Here is what I did: I
>>insert data via JDBC. The timestamp value in the prepared statement is still
>>'2003-08-19 11:40:08.0' as the debugger (of Eclipse) confirms. Inserted in
>>the database gets a tiemstamp 2003-08-19 09:40:08.0'. CURRENT_TIME of
>>DB-Server is an hour back to server time.
>>I used the JDBC-Driver pg80b1.308.jdbc3.jar
>>
>>Today I tried my program using the JDBC-Driver of Postgres 7.4.6
>>(pg74.215.jdbc3.jar). With that JDBC- driver everything works fine. Is there
>>any parameter I missed to set in version 8, or is it a bug?
>>
>>
>>
>
>Yes, this looks like a driver bug, but I don't see an easy way to get
>around it. The problem arises from the fact that you are using a
>timestamp without time zone and the 8.0 driver using the V3 protocol types
>all java.sql.Timestamp objects as timestamp with time zone. In a -08
>timezone note:
>
>Just discards the zone information
>
>jurka=# SELECT '2004-11-29 22:09:59.079-09'::timestamp;
> timestamp
>-------------------------
> 2004-11-29 22:09:59.079
>(1 row)
>
>Converts to the server's zone and then discards.
>
>jurka=# SELECT '2004-11-29 22:09:59.079-09'::timestamptz::timestamp;
> timestamp
>-------------------------
> 2004-11-29 23:09:59.079
>(1 row)
>
>So you by going through the intermediate with time zone type the value is
>changed. For your situation you can just switch to a column type that has
>zone information. To fix this in the driver it seems we would need to be
>aware of the database server's timezone and adjust data going in one
>direction or the other.
>
>The attached test case illustrates the problem by setting up the server to
>be in US Eastern and the java client to be in US Pacific timezones and
>comparing the results when running with the V2/V3 protocols.
>
>Kris Jurka
>
>------------------------------------------------------------------------
>
>import java.sql.*;
>
>public class zone {
>
> public static void main(String args[]) throws Exception {
> Class.forName("org.postgresql.Driver");
>
> // Setup the Java client to be in PST8PDT.
> java.util.TimeZone.setDefault(java.util.TimeZone.getTimeZone("America/Los_Angeles"));
> runWith(2);
> runWith(3);
> }
>
> private static void runWith(int protocolVersion) throws SQLException {
> System.out.println("Running with protocol: " + protocolVersion);
> Connection conn = DriverManager.getConnection("jdbc:postgresql://192.168.3.3:5432/jurka?protocolVersion="+protocolVersion,"jurka","");
>
> Statement stmt = conn.createStatement();
> // Setup the db server to be in EST5EDT.
> stmt.execute("SET TIMEZONE TO 'EST5EDT'");
>
> // Create a test table showing the difference with/without tz.
> stmt.execute("CREATE TEMP TABLE tstest(a timestamp without time zone, b timestamp with time zone)");
>
>
> // This timestamp object will have a timezone of the default
> // java setting we configured earlier.
> Timestamp ts = new Timestamp((new java.util.Date()).getTime());
> System.out.println("Before insert = " + formatTS(ts));
>
> PreparedStatement pstmt = conn.prepareStatement("INSERT INTO tstest(a,b) VALUES(?,?)");
> pstmt.setTimestamp(1, ts);
> pstmt.setTimestamp(2, ts);
> pstmt.executeUpdate();
> pstmt.close();
>
> ResultSet rs = stmt.executeQuery("SELECT a,b FROM tstest");
> while (rs.next()) {
> System.out.println("getString() = getTimestamp()");
> System.out.println(rs.getString(1) + " = " + formatTS(rs.getTimestamp(1)));
> System.out.println(rs.getString(2) + " = " + formatTS(rs.getTimestamp(2)));
> }
> rs.close();
>
> stmt.close();
> conn.close();
> System.out.println();
> }
>
> private static String formatTS(Timestamp ts) {
> int offset = ts.getTimezoneOffset();
> int absoff = Math.abs(offset);
> int hrs = absoff/60;
> int mins = absoff - hrs*60;
> String sign = (offset > 0) ? "-" : "+";
> String hrStr = ((hrs < 10) ? "0" : "") + hrs;
> String minStr = ((mins < 10) ? "0" : "") + mins;
> return ts.toString() + sign + hrStr + ":" + minStr;
> }
>
>}
>
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561