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