BUG #2020: 7.3 -> 8.0.4 migration timestamp problem

From: "Fernando Rubbo" <fernando(at)diuno(dot)com(dot)br>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #2020: 7.3 -> 8.0.4 migration timestamp problem
Date: 2005-11-04 18:23:07
Message-ID: 20051104182307.B774CF1099@svr2.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 2020
Logged by: Fernando Rubbo
Email address: fernando(at)diuno(dot)com(dot)br
PostgreSQL version: 8.0.4
Operating system: Linux - Fedora Core 3
Description: 7.3 -> 8.0.4 migration timestamp problem
Details:

1)
I have a problem with the timestamp before 1914.
When i execute the query by java statement it has no problem, but when i
execute the query
by java preparedstatement the timestamp before 1914 have problems like
this:

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 result in the database is the timestamp equal 1912-12-31 23:53:12.0

PS: i have already debugged the Postgres-8.0 (build 313) driver and it
seems to send the correct date to database.

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(QueryExecutorI
mpl.java:1471)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1256)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)

at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:392)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:330)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statem
ent.java:240)
at teste.Teste3.main(Teste3.java:54)

Browse pgsql-bugs by date

  From Date Subject
Next Message elein 2005-11-05 00:11:42 Re: attislocal value changed with the dump
Previous Message Peter Eisentraut 2005-11-04 16:25:52 Re: Bad French support encoding for postgresql version 8.0.4