Skip site navigation (1) Skip section navigation (2)

Re: getDate after call to updateDate

From: Prasanth <dbadmin(at)nqadmin(dot)com>
To: Oliver Jowett <oliver(at)opencloud(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: getDate after call to updateDate
Date: 2006-01-20 17:55:22
Message-ID: 43D1240A.7030708@nqadmin.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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
>
>  
>

In response to

pgsql-jdbc by date

Next:From: Will SzopkoDate: 2006-01-23 17:02:17
Subject: Inserting an Interval Using JDBC
Previous:From: John DeSoiDate: 2006-01-20 14:01:11
Subject: Re: Need help in installing postgresql 8.1.2 on Windows

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group