pgdev.305.jdbc3, postgresql 8.0 and timestamps
От | Mario Ivankovits |
---|---|
Тема | pgdev.305.jdbc3, postgresql 8.0 and timestamps |
Дата | |
Msg-id | 41239D59.1020806@apache.org обсуждение исходный текст |
Ответы |
Re: pgdev.305.jdbc3, postgresql 8.0 and timestamps
(Dave Cramer <pg@fastcrypt.com>)
Re: pgdev.305.jdbc3, postgresql 8.0 and timestamps (Kris Jurka <books@ejurka.com>) |
Список | pgsql-jdbc |
Hello ! I have a problem with the pgdev.305.jdbc3 driver and timestamps. I created a simple table where the primary key is a timestamp field. Then i inserted a row with the timestamp: 2004-08-17 21:00:00 If i select the record using this date 2004-08-17 21:00:00 it will return the correct row, but a getTimestamp on this row will report 2004-08-17 19:00:00 instead of 2004-08-17 21:00:00. The timestamp in the database shows 2004-08-17 19:00:00. So there is a timezone discrepance between the data sent to the database and the data read back from it. Here you will find a short test for this. The Java snipplet should return date: 2004-08-17 21:00:00.0/1092769200000 update: 1 DateA: 2004-08-17 21:00:00.0/1092769200000 DateB: 2004-08-17 21:00:00.0/1092769200000 but it returns date: 2004-08-17 21:00:00.0/1092769200000 update: 1 DateA: 2004-08-17 19:00:00.0/1092762000000 I can wokaround it by using "timestamptz" instead of "timestamp" or using the "pg74.1jdbc3.jar" driver. I found this problem with hibernate (www.hibernate.org) when it tries to delete an object but the database cant find it. I just tried to break down the sequence to plain jdbc calls - and here it is: CREATE TABLE tt ( ttt timestamp NOT NULL, CONSTRAINT ttpkey PRIMARY KEY (ttt) ) WITHOUT OIDS; public static void main(String[] args) throws Exception { Class.forName("org.postgresql.Driver"); Timestamp dd = new java.sql.Timestamp(new Date(2004-1900, 8-1, 17, 21, 0, 0).getTime()); System.err.println("date: " + dd + "/" + dd.getTime()); Connection con = DriverManager.getConnection("jdbc:postgresql:db", "usr", "pass"); PreparedStatement stm = con.prepareStatement("insert into tt values(?)"); stm.setTimestamp(1, dd); System.err.println("update: " + stm.executeUpdate()); stm = con.prepareStatement("select * from tt where ttt = ?"); stm.setTimestamp(1, dd); ResultSet rs = stm.executeQuery(); while (rs.next()) { dd = rs.getTimestamp("ttt"); System.err.println("DateA: " + dd + "/" + dd.getTime()); } rs.close(); stm = con.prepareStatement("select * from tt where ttt = ?"); stm.setTimestamp(1, dd); rs = stm.executeQuery(); while (rs.next()) { Date d = rs.getTimestamp("ttt"); System.err.println("DateB: " + d + "/" + d.getTime()); } rs.close(); } Ciao, Mario
В списке pgsql-jdbc по дате отправления: