Skip site navigation (1) Skip section navigation (2)

pgdev.305.jdbc3, postgresql 8.0 and timestamps

From: Mario Ivankovits <imario(at)apache(dot)org>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: pgdev.305.jdbc3, postgresql 8.0 and timestamps
Date: 2004-08-18 18:18:01
Message-ID: 41239D59.1020806@apache.org (view raw or flat)
Thread:
Lists: 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


Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2004-08-18 19:51:34
Subject: Re: pgdev.305.jdbc3, postgresql 8.0 and timestamps
Previous:From: Dave CramerDate: 2004-08-17 15:52:45
Subject: Re: Advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group