7.3 -> 8.0.4 migration timestamp problem

From: Eliézer Madeira de Campos <eliezer(at)diuno(dot)com(dot)br>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: 7.3 -> 8.0.4 migration timestamp problem
Date: 2005-11-04 18:35:34
Message-ID: 54CC7CADE26E884EB2BCEC863F5C1F70F9D263@moscou.diuno.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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 date stored, 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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2005-11-04 22:20:03 Re: 7.3 -> 8.0.4 migration timestamp problem
Previous Message Sergey Pariev 2005-11-04 09:34:35 Re: Status of COPY support patch