Обсуждение: 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 + "')");
        }
    }
}