Re: getDate after call to updateDate

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(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
>>
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>