PostgreSQL JDBC - getDate after call to updateDate

Author

getDate after call to updateDate

Prasanth

2006-01-16, 8:24 pm

But when I use the latest 8.1-404 or even 8.2dev jdbc3 drivers I am
observing a strange result.

When I update a date in the resultset (say with 12/31/2005) and then
call getDate it returns a date which is one day behind the value I have
set (returns 12/30/2005).
But it updates the database with the right date (12/31/2005). If I re
fetch the same row then I can see the right value.

I am running 7.4.7 version.

Below if the code to reproduce the error.

import java.sql.*;
import java.util.Calendar;

public class PostgresDate {
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver");

Connection conn =
DriverManager.getConnection("jdbc:postgresql://databases.nqadmin.com:5432/test_server",
"postgres", "opelgt");
Statement stmt =
conn. createStatement(Resu
ltSet. TYPE_SCROLL_INSENSIT
IVE,
ResultSet.CONCUR_UPDATABLE);

try {
stmt.executeUpdate("DROP TABLE testdate2");
} catch (SQLException e) {}

stmt.executeUpdate("CREATE TABLE testdate2(id int4 primary key,
d date)");
stmt.executeUpdate("INSERT INTO testdate2(id, d) VALUES
(1,'2005-02-10')");

Calendar c = Calendar.getInstance();
c.set(Calendar.MONTH, 1);
c.set(Calendar.DATE, 2);
c.set(Calendar.YEAR, 2005);

Date d = new Date(c.getTimeInMillis());

ResultSet rs = stmt.executeQuery("SELECT * FROM testdate2");
rs.next();
rs.updateDate("d", d);
rs.updateRow();
d = rs.getDate("d");
System.out.println("Got date: " + d);

rs = stmt.executeQuery("SELECT * FROM testdate2");
rs.next();
d = rs.getDate("d");
System.out.println("Date after refresh: " + d);
rs.close();

stmt.close();
conn.close();
}
}

Thanks,
-Prasanth.

Prasanth wrote:

>Hi Oliver,
>
>Thank you very much.
>
>-Prasanth.
>
>Oliver Jowett wrote:
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster
>
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Oliver Jowett

2006-01-16, 8:24 pm

Prasanth wrote:
> But when I use the latest 8.1-404 or even 8.2dev jdbc3 drivers I am
> observing a strange result.
>
> When I update a date in the resultset (say with 12/31/2005) and then
> call getDate it returns a date which is one day behind the value I have
> set (returns 12/30/2005).
> But it updates the database with the right date (12/31/2005). If I re
> fetch the same row then I can see the right value.

I couldn't reproduce this with your testcase. Possibly the effect you're
seeing is because you're not creating a strictly correct Date object:

> Calendar c = Calendar.getInstance();

This gives you a Calendar for the current system time.

> c.set(Calendar.MONTH, 1);
> c.set(Calendar.DATE, 2);
> c.set(Calendar.YEAR, 2005);

This resets the date to 2005/02/01, but leaves the time portion unchanged.
[color=darkred]
> Date d = new Date(c. getTimeInMillis());[
/color]

This creates a java.sql.Date object that has a non-zero
hour/minute/second, which strictly speaking you're not meant to have.
The javadoc for java.sql.Date says:
[color=darkred]

Can you try a modified testcase that resets
hours/minutes/seconds/milliseconds on the calendar before getting the
millisecond value?

I'm not quite sure why this would cause the problem you're seeing,
though.. One other thing to try is to print the before/after values of
d.getTime(), which gives more exact information than Date.toString() does.

Also: what are the default timezones for your JVM and server?

-O

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Prasanth

2006-01-20, 1:24 pm

I have tried this with 8.0.3 server and I get the same result.

Any idea as to what could be wrong?

Thanks,
-Prasanth.

Prasanth wrote:

>The time zone on the JVM is CST. On the postgres it is not set in the conf file
>so I assume it defaults to system setting which is set to CST.
>
>Below is the modified code:
>
>The result is:
>Setting Date To: 1107356400000 -> 2005-2-2
>From ResultSet: 1107302400000 -> 2005-02-01
>After refetch: 1107324000000 -> 2005-02-02
>
>
>
>import java.sql.*;
>import java.util.Calendar;
>
>public class PostgresDate {
> public static void main(String[] args) throws Exception {
> Class.forName("org.postgresql.Driver");
>
> Connection conn =
>DriverManager.getConnection("jdbc:postgresql://databases:5432/test_server");
> Statement stmt = conn. createStatement(Resu
ltSet. TYPE_SCROLL_INSENSIT
IVE,
>ResultSet.CONCUR_UPDATABLE);
>
> try {
> stmt.executeUpdate("DROP TABLE testdate2");
> } catch (SQLException e) {}
>
> stmt.executeUpdate("CREATE TABLE testdate2(id int4 primary key, d date)");
> stmt.executeUpdate("INSERT INTO testdate2(id, d) VALUES (1,'2005-02-10')");
>
>
> Calendar c = Calendar.getInstance();
> c.set(Calendar.MONTH, 1);
> c.set(Calendar.DATE, 2);
> c.set(Calendar.YEAR, 2005);
> c.set(Calendar.HOUR_OF_DAY,0);
> c.set(Calendar.MINUTE, 0);
> c.set(Calendar.SECOND, 0);
> c.set(Calendar.MILLISECOND, 0);
> c.set(Calendar.AM_PM, Calendar.AM);
>
> System.out.println("Setting Date To: " + c.getTimeInMillis() + " -> " +
>c.get(Calendar.YEAR) + "-" + (c.get(Calendar.MONTH)+1) + "-" +
>c.get(Calendar.DAY_OF_MONTH));
>
> Date d = new Date(c.getTimeInMillis());
>
> ResultSet rs = stmt.executeQuery("SELECT * FROM testdate2");
> rs.next();
> rs.updateDate("d", d);
> rs.updateRow();
> d = rs.getDate("d");
> System.out.println("From ResultSet: " + d.getTime() + " -> " + d);
>
>
> rs = stmt.executeQuery("SELECT * FROM testdate2");
> rs.next();
> d = rs.getDate("d");
> System.out.println("After refetch: " + d.getTime() + " -> " + d);
> rs.close();
>
> stmt.close();
> conn.close();
> }
>}
>
>Oliver Jowett wrote:
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links