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
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)
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