Re: getDate after call to updateDate

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.nqadmin.com:5432/test_server",
"postgres", "opelgt");
Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
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:
> 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.
>
>> Date d = new Date(c.getTimeInMillis());
>
>
> 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:
>
>>> To conform with the definition of SQL DATE, the millisecond values
>>> wrapped by a java.sql.Date instance must be 'normalized' by setting the
>>> hours, minutes, seconds, and milliseconds to zero in the particular time
>>> zone with which the instance is associated.
>
>
> 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
>