PostgreSQL JDBC - 7.3 -> 8.0.4 migration timestamp problem

Author

7.3 -> 8.0.4 migration timestamp problem

Eliézer Madeira de Campos

2005-11-04, 8:24 pm

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(19
13, 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(Query
ExecutorImpl.java:1256)
at org.postgresql.core.v3.QueryExecutorImpl. execute(QueryExecuto
rImpl.java:175)
at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. execute(AbstractJdbc
2Statement.java:392)
at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. executeWithFlags(Abs
tractJdbc2Statement.java:330)
at org.postgresql.jdbc2. AbstractJdbc2Stateme
nt. executeQuery(Abstrac
tJdbc2Statement.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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Eliézer Madeira de Campos

2005-11-04, 8:24 pm

-----Original Message-----
From: Oliver Jowett & #91;mailto:oliver@op
encloud.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:

[color=darkred]
> What type is the target column you are inserting into?
Timestamp without timezone.

[color=darkred]
> 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 solution to the problem.

Eliézer M de Campos

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Oliver Jowett

2005-11-04, 8:24 pm

Eliézer Madeira de Campos wrote:

>
>

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Oliver Jowett

2005-11-04, 8:24 pm

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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Oliver Jowett

2005-11-08, 4:12 pm

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(19
13, 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:tes
t?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

Sponsored Links