Re: Timestamp problems - Mailing list pgsql-jdbc

Peter,

I looked at the sample program you sent (thanks for the test program, it
makes it much easier to understand).  From what I can tell the driver is
working correctly.

The data type of the column datum that you create is 'date'.  Since a
'date' does not store any time information the rest of the behavior you
see is related to this fact.

So why are you using setTimestamp/getTimestamp when the data type is
date?  You should be using setDate/getDate.

If I either change the data type to 'timestamp' in your program, or use
getDate/setDate with the 'date' data type everything works as I would
expect.

The fact that you are mixing dates and timestamps I think is causing
your problems.  However I believe the driver is functioning correctly
givin what your code is actually doing.

thanks,
--Barry

Peter Katzmann wrote:
> Hello Attached is the example with the same error and here is a small
> database log:
> 2003-02-07 14:32:32 [17354]  LOG:  connection received:
> host=192.168.2.11 port=33900
> 2003-02-07 14:32:32 [17354]  LOG:  connection authorized: user=postgres
> database=achievo
> 2003-02-07 14:32:32 [17354]  LOG:  query: set datestyle to 'ISO'; select
> version(), case when pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN'
> else getdatabaseencoding() end;
> 2003-02-07 14:32:32 [17354]  LOG:  query: create temp table tmp ( datum
> date, idx int)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.668000000+00', 0)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.913000000+00', 1)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.916000000+00', 2)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.919000000+00', 3)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.921000000+00', 4)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.923000000+00', 5)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.926000000+00', 6)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.928000000+00', 7)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.939000000+00', 8)
> 2003-02-07 14:32:32 [17354]  LOG:  query: insert into tmp
> Values('2003-02-07 13:32:31.941000000+00', 9)
> 2003-02-07 14:32:32 [17354]  LOG:  query: select * from tmp
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=0
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=1
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=2
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=3
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=4
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=5
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=6
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=7
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=8
> 2003-02-07 14:32:32 [17354]  LOG:  query: update tmp set
> datum='2003-02-06 23:00:00.000000000+00' where idx=9
>
>
> Barry Lind wrote:
>
>> Peter,
>>
>> When you say you tried with the current version what do you mean?
>> Specifically have you tried the latest 7.3 build from
>> jdbc.postgresql.org?  There were some recent bug fixes in this area
>> that I think may fix your problem.
>>
>> If the current code still has this problem, can you submit a test case
>> that we can compile and run that demonstrates the problem?
>>
>> thanks,
>> --Barry
>>
>>
>> Peter Katzmann wrote:
>>
>>> High,
>>> i' currently working with hibernate. They represent Calendar types as
>>> timestamps. During prepareStatement and setTimestamp the data
>>> generated for
>>> the query has a modified date. The date is something original:
>>>
>>>
>>> insert into hours  ( entrydate, activitydate, remark, time, userid,
>>> checked,
>>> phaseid, activityid, zkub, id ) values  '2003-02-06
>>> 11:11:29.338000000+00',
>>> '2003-02-06 11:11:29.338000000+00', 'öfölsdajklö', 120, 'pk1057',
>>> 'f', null,
>>> null, null, 12747 )
>>>
>>>
>>> And this will be aftter the data was read back, the checked flag
>>> changed and
>>> wrote back
>>>
>>> update hours set entrydate = '2003-02-05 23:00:00.000000000+00',
>>> activitydate
>>> = '2003-02-05 23:00:00.000000000+00', remark = 'öfölsdajklö', time =
>>> 120,
>>> userid = 'pk1057', checked = 't', phaseid = 5, activityid = 3, zkub =
>>> ' '
>>> where id = 12747
>>>
>>> First occured the problem with postgres 7.2 so i updated to a current
>>> version,
>>> but no go either.
>>>
>>>
>>> peter
>>>
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 5: Have you checked our extensive FAQ?
>>>
>>> http://www.postgresql.org/users-lounge/docs/faq.html
>>>
>>>
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>
>
>
> ------------------------------------------------------------------------
>
> /*
>  * TryOut.java
>  *
>  * Created on 7. Februar 2003, 13:36
>  */
>
> import java.sql.*;
>
>
> /**
>  *
>  * @author  root
>  */
> public class TryOut {
>   private java.sql.Connection dbConnection = null;
>
>   /** Die URL zur Datenbank */
>   private String dbURL = null;
>   /** Der Datenbank-Banutzer */
>   private String dbUser = null;
>   /** Das Password des Datenbank-Benutzers */
>   private String dbPasswd = null;
>   /***********************
>    * simple table of the form
>    *
>    *
>    * /** Creates a new instance of TryOut */
>   public TryOut() {
>   }
>
>   /**
>    * @param args the command line arguments
>    */
>   public static void main(String[] args) {
>     TryOut tr = new TryOut();
>     tr.doIt();
>   }
>
>   private void doIt() {
>     try {
>       Class.forName("org.postgresql.Driver");
>       dbConnect("jdbc:postgresql://kontor.thiesen.de:5432/achievo?charSet=ISO-8859-15", "postgres", "postgres");
>       Statement state = dbConnection.createStatement();
>       state.execute("create temp table tmp ( datum date, idx int)");
>       PreparedStatement st1 = dbConnection.prepareStatement("insert into tmp Values(?, ?)");
>       PreparedStatement st2 = dbConnection.prepareStatement("update tmp set datum=? where idx=?");
>
>       for (int i = 0; i < 10; i++) {
>         st1.setInt(2, i);
>         st1.setTimestamp(1,new Timestamp(new java.util.Date().getTime()));
>         st1.execute();
>       }
>       ResultSet rs = state.executeQuery("select * from tmp");
>       for (int i = 0; i < 10; i++) {
>         rs.absolute(i+1);
>         st2.setTimestamp(1, new Timestamp(rs.getDate(1).getTime()));
>         st2.setInt(2, i);
>         st2.execute();
>       }
>       dbConnection.close();
>     } catch (Exception e) {
>       e.printStackTrace();
>     }
>   }
>
>    /* Oeffnet die Datenbankverbindung
>     *
>     * @param dbUrl die JDBC-RessourcenURL
>     * @param dbUser dbUser
>     * @param dbPasswd
>     *
>     * @exception SQLException noch Fragen?
>     */
>
>
>   public void dbConnect(String dbURL, String dbUsr, String dbPwd)  {
>     this.dbURL = dbURL;
>     this.dbUser = dbUsr;
>     this.dbPasswd = dbPwd;
>
>     dbConnect();
>   }
>
>   /**
>    * Oeffnet die Datenbankverbindung
>    *
>    * @param dbUrl die JDBC-RessourcenURL
>    * @param dbUser dbUser
>    * @param dbPasswd
>    *
>    * @exception SQLException noch Fragen?
>    */
>   private void dbConnect() {
>     /* Verbindung zur Datenbank aufbauen */
>     //        getMainSession().getDebug().message(this, "DriverManager.getConnection() "+dbURL+" "+dbUser+"
********");
>     System.out.println( "DriverManager.getConnection() "+dbURL+" "+dbUser+" ********");
>     try {
>       this.dbConnection = DriverManager.getConnection(dbURL, dbUser, dbPasswd);
>     } catch (SQLException se) {
>       se.printStackTrace();
>     }
>
>     try {
>       DatabaseMetaData dmd = dbConnection.getMetaData();
>       String dbi = new String();
>
>       dbi = dbi.concat("---            database: "+dmd.getDatabaseProductName()+"\n");
>       dbi = dbi.concat("---     databse version: "+dmd.getDatabaseProductVersion()+"\n");
>       dbi = dbi.concat("---    JDBC driver name: "+dmd.getDriverName()+"\n");
>       dbi = dbi.concat("--- JDBC driver version: "+dmd.getDriverVersion());
>       //getMainSession().getDebug().message(this, "Database Info:\n"+dbi);
>       System.out.println( "Database Info:\n"+dbi);
>     } catch (SQLException se) {
>       se.printStackTrace();
>     }
>
>   }
>
> }