Thread: Timestamp problems
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
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 > >
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();
> }
>
> }
>
> }
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
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
>>
>
>