7.3 -> 8.0.4 migration timestamp problem

Поиск
Список
Период
Сортировка
От Eliézer Madeira de Campos
Тема 7.3 -> 8.0.4 migration timestamp problem
Дата
Msg-id 54CC7CADE26E884EB2BCEC863F5C1F70F9D263@moscou.diuno.net
обсуждение исходный текст
Ответы Re: 7.3 -> 8.0.4 migration timestamp problem  (Oliver Jowett <oliver@opencloud.com>)
Re: 7.3 -> 8.0.4 migration timestamp problem  (Oliver Jowett <oliver@opencloud.com>)
Список 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
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

В списке pgsql-jdbc по дате отправления:

Предыдущее
От: Sergey Pariev
Дата:
Сообщение: Re: Status of COPY support patch
Следующее
От: Eliézer Madeira de Campos
Дата:
Сообщение: Re: 7.3 -> 8.0.4 migration timestamp problem