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
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 |