Re: pgdev.305.jdbc3, postgresql 8.0 and timestamps

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

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

On Wed, 18 Aug 2004, 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.
>

I have not been able to duplicate this problem. What server version are
you running this against? Is there something else unusual in your setup?
Does the java client believe it is in a different timezone than the
database? Compare SELECT now() in psql versus
System.out.println(new java.text.SimpleDateFormat("Z").format(new Date()))

Kris Jurka