SEC_TO_TIME conversion

[rajat.ray]

How can i calculate miliseconds to HH:MM:SS format in iReport.

I can do this by executing this query

 

SELECT SEC_TO_TIME( SUM( SEC_TO_TIME( `totalTime` ) ) ) AS totalTime FROM individualprofiletable;

 

But when i execute this query in iReport it converts the output to 1/1/70 12:03AM.

 

Plz help me out.

[javydreamercsw]

Verify the format of the parameter. Looks like it's date format, change it to Time.

[leroyg]

Hi

 

I posted the same problem last week. The only way I could convert unix timestamp to readable time was to create a static method in an external class mystuff.java:

 

public static String getSQLDate(Long today) {

 

Timestamp EndDate = new Timestamp(Long.parseLong(today+"")*1000);

return EndDate.toString();

}

 

In the text field expression call your method

mystuff.getSQLDate($F{CREATED_T}). set your tesx field expression class as a String.

 

Works for me

Cheers

[rajat.ray]

totalTime is nothing but the total number of seconds.

[bklawans]

Brute force might be the best answer then. Create a String field with the expression:

 

Code:

new String ( ($F{totalTime}.longValue() / 3600) + ":" +
  (($F{totalTime}.longValue() % 3600) / 60))

 

or something to that effect. If totalTime is milliseconds and not seconds just replace the hard coded constants with 3600000 and 60000.

 

-Barry

[rajat.ray]

Hi,

 

My query is like this:

 

And I want to convert individualprofiletable.totalTime into HH:MM:SS.

 

Select distinct individualprofiletable.firstname, individualprofiletable.middlename, individualprofiletable.lastname,individualprofiletable.totalTime,usertable.alias From individualprofiletable,registrationpintable,usertable where usertable.user_id=registrationpintable.user_id && individualprofiletable.userid=registrationpintable.user_id && college_name='vtu_college1';

 

Plz help me.