Re: Timestamp problems - Mailing list pgsql-jdbc

Peter,

Converting from a date to a timestamp and back can be difficult.  The
reason is timezones.  java.sql.Date does not have a concept of timezone,
but java.sql.Timestamp does.  The timezone offset can easily move the
date portion of a timestamp to a different date.

As I said in my mail note, I believe the jdbc driver is behaving
according to spec.  I certainly could be wrong however.  I need you to
explain exactly what behavior you think is not compliant with the spec
and why, along with providing examples as appropriate.

thanks,
--Barry


Peter Katzmann wrote:
> 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
>>
>
>