CODEWITHSUNDEEP
javamysql
chun
chun

Reputation: 847

SEC_TO_TIME() convert to java.sql.Time error

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...?

Upvotes: 1

Views: 1765

Answers (3)

ritwik
ritwik

Reputation: 1

Try this:

CONCAT('',TIMEDIFF(exp2,NOW()))

Upvotes: 0

Sean Owen
Sean Owen

Reputation: 66886

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.

Upvotes: 1

Paul Tomblin
Paul Tomblin

Reputation: 182802

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.

Upvotes: 0

Related Questions