Re: Timestamp problems - Mailing list pgsql-jdbc

Barry,
the problem is that hibernate does this mapping.
I have the data stored as date and wan't to get the Calendar type, this one
will mapped to timestamp.
For my understanding it should be no problem to convert a date to timestamp
and back. The date should always be the same, shouldn't it ?
Both represent a Calendar Date.

peter

Barry Lind wrote:
> 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();
>>     }
>>       }
>>   }
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


--
Thiesen hardware & software GmbH | P. Katzmann
Bereichsleiter SW Entwicklung    | p.katzmann@thiesen.com
Im Tiegel 9              | www.thiesen.com
36367 Wartenberg         | tel. +49 (0) 6641 979 140  fax +49 (0) 6641 979 299