JDBC vs psql interval rendering differences

Question

Using postgresql 9.1 with linux psql console and the windows pgadminIII the query:

select '2012-05-05 15:57:31-07'::timestamp with time zone - '2012-05-01 23:13:34-07'::timestamp with time zone;

renders the return as:

    ?column?     
-----------------
 3 days 16:43:57
(1 row)

However, the windows JDBC driver (specifically using jasperreport's ireport) renders this same query as:

    ?column?     
-----------------
 0 years 0 mons 3 days 16 hours 43 mins 57.00 secs
(1 row)

I've explored to_char and the postgresql documentation but cannot find a solution. Two questions: First, how can I make the JDBC driver render the interval with the same formatting as pqsl?

Second, can I instead make both psql and JDBC render the result as:

88:43:57  

where hours keep counting into the 100s as the interval grows (its a silly industry standard...)

Answer 1

"First, how can I make the JDBC driver render the interval with the same formatting as pqsl?"
I think this question is not actually well-defined. An object of type org.postgresql.util.PGInterval does not have a format. Of course if you call toString() on it, then you'll get something. You'll get what iReport shows. So in a sense this is its default format. But really it is just a bunch of ints for year, month, day, hour, minute, and a double for seconds. It's not a formatted thing.

pgAdmin III does some magic to format it more nicely. But this magic isn't built in to the JDBC driver. Szymon's answer will certainly work. But... blech.

"Second, can I instead make both psql and JDBC render the result as: 88:43:57"
You could put the logic into the .jrxml instead of Szymon's procedure. This would be better because... well... it wouldn't really be better. It would just be different. It would let you leave your SQL query unmodified by calculating this in the report:

$F{?column?}.getDays() * 24 + $F{?column?}.getHours()
+ ":" + $F{?column?}.getMinutes() + ":" + $F{?column?}.getSeconds()

That's not 100% correct. You need to truncate (round?) the seconds from a double to an int for your requirement. And to be completely generic you need to add in months and years, but maybe you don't really care about those. Anyway, you could do it like this. Caveat intervalor.