Postgres Pro
Downloads
Home   >   mailing lists

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

aggregate functions and timestamps in JDBC, possible bug?

From
"Tomi Panula-Ontto"
Date:
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
 
 
 

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

From
"Paul Stead"
Date:
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@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Tomi Panula-Ontto
Sent: Saturday, August 03, 2002 6:23 AM
To: pgsql-jdbc@postgresql.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