Re: pgdev.305.jdbc3, postgresql 8.0 and timestamps - Mailing list pgsql-jdbc

Mario,

I'm guessing your current timezone offset is +2 ?

What do the logs in postgresql say ? I'm interested to see if the driver
is changing the data or postgresql is changing the data on input.

Dave
On Wed, 2004-08-18 at 14:18, Mario Ivankovits wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
Dave Cramer
519 939 0336
ICQ # 14675561