Re: getDate after call to updateDate
От | Prasanth |
---|---|
Тема | Re: getDate after call to updateDate |
Дата | |
Msg-id | 43D1240A.7030708@nqadmin.com обсуждение исходный текст |
Ответ на | Re: getDate after call to updateDate (Prasanth <dbadmin@nqadmin.com>) |
Список | 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 > > >
В списке pgsql-jdbc по дате отправления:
Предыдущее
От: John DeSoiДата:
Сообщение: Re: [ADMIN] Need help in installing postgresql 8.1.2 on Windows