Re: aggregate functions and timestamps in JDBC, possible bug?

I don't consider myself an expert in SQL or PostgreSQL, but....

Aren't you getting an INTERVAL back from your query, not a timestamp??
The reason it's telling you that it's a format error at 2 is that it expects
another digit to be there, not a colon ( : )

FWIW, due to the variances between different SQL DBMS's and their handling
of Intervals, I always do the date comparisons and date arithmetic in Java
(our stuff cannot be written for one DBMS in particular, it's why we use
Java and JDBC in the first place).

If anyone has other ideas on using INTERVALS with different SQL DMBS's, I'd
love to hear about it too! :-)

cheers,
Paul
-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Tomi Panula-Ontto
Sent: Saturday, August 03, 2002 6:23 AM
To: pgsql-jdbc(at)postgresql(dot)org
Subject: [JDBC] aggregate functions and timestamps in JDBC, possible bug?

Hello,
I have a problem with PostgreSQL JDBC driver when using timestamps.

Consider this:

CREATE TABLE xyz (
starttime timestamp not null,
endtime timestamp not null
);

INSERT INTO xyz VALUES ( '2002-08-1 03:00:00+03', '2002-08-1
06:00:00+03' );
INSERT INTO xyz VALUES ( '2002-08-2 03:20:00+03', '2002-08-2
06:30:00+03' );

Then do:

SELECT SUM(endtime - starttime) FROM xyz;

And you'll get:

sum
-------
06:10
(1 row)

(Which means 6 hours and 10 minutes).

Then try to read that field using JDBC using something like:
java.sql.Timestamp ts = rs.getTimestamp(1);

And you'll get my problem:

Bad Timestamp Format at 2 in 06:10
at org.postgresql.jdbc2.ResultSet.toTimestamp(ResultSet.java:1705)
at org.postgresql.jdbc2.ResultSet.getTimestamp(ResultSet.java:398)

I am using latest stable JDBC driver from the
http://jdbc.postgresql.org/download/pgjdbc2.jar
md5 checksum for my current version:
f4ed2deaa88e16e79ccfa1c5b1f5ca22 *pgjdbc2.jar

Any ideas, tips, help? Anyone know how to get the expected timestamp?
(In my mind it's a parsing bug in the driver, but I let somebody else make
the judgement.)

Thanks for any help,
Tomi