Re: Timestamp problems - Mailing list pgsql-jdbc

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. But anyway, why is the timestamp manipulated.
Didn't i understand it right that the timezone offset to gmt has to be added at
the end, and here it should be +1 one and not +0

peter

Barry Lind <blind@xythos.com> schrieb:

> 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
>
>




--
Peter Katzmann