SEC_TO_TIME() convert to java.sql.Time error

Question

hi I have a aggregate column present the microsecond, a report(with jasper) have to show HH:mm:ss of this indicator

What I did is using SEC_TO_TIME(sum(col)/1000) , but when mapping to java.sql.Time, i doesn't work when the value of hour in result pass over 24(ex:36:33:33)

Then I think another way, not using sec_to_time, just mapping the microsecond as Bigdecimal, but dunno what java class shoud i use to format date as the default format of hh:mm:ss is limit to 24...?

Answer 1

The constructor for java.sql.Time takes a time in milliseconds. So why can't you just multiply the microsecond result by 1000 to make milliseconds and pass that in? Once you have the java Date, you can format it with DateFormat.

Answer 2

java.sql.Time represents a point in time. You are trying to represent a duration, not a point in time. So, trying to "convert" this to java.sql.Time is conceptually wrong, and not surprisingly you are seeing errors as a result.

SEC_TO_TIME does all the formatting you need, right? So, just select that expression and retrieve it from the ResultSet as a String.

Alternatively, if you want to represent and format durations of time in Java instead of SQL, please please use Joda Time.