Thread: 7.3 -> 8.0.4 migration timestamp problem
Hello everyone: I'm currently working to migrate a database from PostgreSQL 7.3 to 8.0.4. I have found the two issues below: 1) Timestamp before 1914. When I execute the query by java statement no problem occurs, but when I execute the query by java preparedstatement this is what happens: PreparedStatement pst = con.prepareStatement("insert into teste values (?, ?, ?)"); pst.setObject(1, new Integer(1)); pst.setObject(2, "TESTE"); Calendar c = new GregorianCalendar(1913, 0, 1, 0, 0, 0); Timestamp t = new Timestamp(c.getTimeInMillis()); pst.setTimestamp(3, t); pst.executeUpdate(); The date stored in database is actually 1912-12-31 23:53:12.0 (however it should have stored 1913-01-01. I have already debugged the Postgres-8.0 (build 313) driver and it seems to send the correct date to database. This problem occurs both with Windows and Linux versions of PostgreSQL 8. There is a small difference in the actual datestored, but in both systems it is not the date I'm trying to store. 2) Another problem is with functions like date_trunc and date_part that use a timestamp parameter. The problem ocurrs only with the java preparedstatment. Calendar c = new GregorianCalendar(); Timestamp ts = new Timestamp(c.getTimeInMillis()); pst = con.prepareStatement("select date_trunc('day', TIMESTAMP ?)"); pst.setObject(1, ts); rs = pst.executeQuery(); below the printstacktrace: Exception in thread "main" java.sql.SQLException: ERROR: syntax error at or near "$1" at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471) at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256) at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175) at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:392) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330) at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240) at teste.Teste3.main(Teste3.java:54) This behaviour is exactly the same both in Windows and Linux. Any ideas? Eliézer M de Campos/Fernando Rubbo
-----Original Message----- From: Oliver Jowett [mailto:oliver@opencloud.com] Sent: sexta-feira, 4 de novembro de 2005 20:20 To: Eliézer Madeira de Campos Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] 7.3 -> 8.0.4 migration timestamp problem Eliézer Madeira de Campos wrote: >> The date stored in database is actually 1912-12-31 23:53:12.0 (however it should have stored 1913-01-01. >> >> I have already debugged the Postgres-8.0 (build 313) driver and it seems to send the correct date to database. > What type is the target column you are inserting into? Timestamp without timezone. >> Timestamp ts = new Timestamp(c.getTimeInMillis()); >> pst = con.prepareStatement("select date_trunc('day', TIMESTAMP ?)"); >> pst.setObject(1, ts); > Use "CAST (? AS TIMESTAMP)" instead of "TIMESTAMP ?". Why should I, if "TIMESTAMP ?" works when I run the insert in psql (or via unprepared statement)? That might be valid as a workaround, but it would cost me thousands changes in the application, so it's not really a solutionto the problem. Eliézer M de Campos
Eliézer Madeira de Campos wrote: >>> Timestamp ts = new Timestamp(c.getTimeInMillis()); >>> pst = con.prepareStatement("select date_trunc('day', TIMESTAMP ?)"); >>> pst.setObject(1, ts); > > >>Use "CAST (? AS TIMESTAMP)" instead of "TIMESTAMP ?". > > Why should I, if "TIMESTAMP ?" works when I run the insert in psql (or via unprepared statement)? There are lots of other places where you can't blindly use '?' placeholders -- for example, you can't use them where a column or table name is expected. Prepared statements aren't just textual substitution. The 8.0 driver's implementation uses protocol-level parameter binding that is roughly equivalent to PREPARE/EXECUTE at the SQL level. Try that same query via PREPARE in psql and you will see that it fails in the same way. > That might be valid as a workaround, but it would cost me thousands changes in the application, so it's not really a solutionto the problem. You need to talk to the backend developers then -- it's a limitation of the SQL grammar used by the backend. As a workaround, set protocolVersion=2 as a URL parameter, but you will lose other driver functionality if you do that (e.g. parameter metadata), and the v2 protocol path will not stay around forever. -O
Eliézer Madeira de Campos wrote: > The date stored in database is actually 1912-12-31 23:53:12.0 (however it should have stored 1913-01-01. > > I have already debugged the Postgres-8.0 (build 313) driver and it seems to send the correct date to database. What type is the target column you are inserting into? > Timestamp ts = new Timestamp(c.getTimeInMillis()); > pst = con.prepareStatement("select date_trunc('day', TIMESTAMP ?)"); > pst.setObject(1, ts); Use "CAST (? AS TIMESTAMP)" instead of "TIMESTAMP ?". -O
Eliézer Madeira de Campos wrote: > 1) Timestamp before 1914. > When I execute the query by java statement no problem occurs, but when I execute the query > by java preparedstatement this is what happens: > > PreparedStatement pst = con.prepareStatement("insert into teste values (?, ?, ?)"); > pst.setObject(1, new Integer(1)); > pst.setObject(2, "TESTE"); > Calendar c = new GregorianCalendar(1913, 0, 1, 0, 0, 0); > Timestamp t = new Timestamp(c.getTimeInMillis()); > pst.setTimestamp(3, t); > pst.executeUpdate(); > > The date stored in database is actually 1912-12-31 23:53:12.0 (however it should have stored 1913-01-01. > > I have already debugged the Postgres-8.0 (build 313) driver and it seems to send the correct date to database. I could not reproduce this problem using the attached testcase against 8.0.3 and the build 313 driver. I get the expected output: > $ java -classpath .:postgresql-8.0-313.jdbc3.jar TestTimestamp6 'jdbc:postgresql:test?user=oliver' > 1 => 1913-01-01 00:00:00.0 (literal '1913-01-01 00:00:00') What are you doing differently? Perhaps the JVM and server timezone settings are important, what are you using? -O import java.sql.*; import java.util.*; import java.text.*; public class TestTimestamp6 { public static void main(String[] args) throws Exception { Class.forName("org.postgresql.Driver"); Connection conn = DriverManager.getConnection(args[0]); Statement stmt = conn.createStatement(); try { stmt.executeUpdate("DROP TABLE testtimestamp6"); } catch (SQLException e) {} stmt.executeUpdate("CREATE TABLE testtimestamp6(index int4, datetime timestamp without time zone)"); PreparedStatement pst = conn.prepareStatement("insert into testtimestamp6 values (?, ?)"); pst.setObject(1, new Integer(1)); Calendar c = new GregorianCalendar(1913, 0, 1, 0, 0, 0); Timestamp t = new Timestamp(c.getTimeInMillis()); pst.setTimestamp(2, t); pst.executeUpdate(); ResultSet rs = stmt.executeQuery("select * from testtimestamp6"); while (rs.next()) { int index = rs.getInt(1); Timestamp ts = rs.getTimestamp(2); String ts_text = rs.getString(2); System.out.println(index + " => " + ts + " (literal '" + ts_text + "')"); } } }