formatting milliseconds as hh:mm:ss in ireport

[GuyWithDogs]

I'm trying to format a computed duration (in milliseconds) into an hh:mm:ss.zzz appearance in a report.

 

The computed value comes from doing a calculation like this:

 

Code:

select machine, date(rundate), sum(acttime) from datatable
group by machine, date(rundate)

 

I end up with a set of information like

 

Machine1 12/02/07 34565.0

Machine2 12/02/07 3523525.0

Machine1 13/02/07 3623423.0

Machine2 13/02/07 etc...

 

The SUM() comes back as a double - sort of surprised me, but it's been a while since I've messed around with aggregate functions.

 

Anyway, I've tried Google and various MySQL forums for a way of converting the millisecond value using the database functions. Got nothing but partial successes. It's possible, because of the "slop" in the timing of the various entries that get added for a given day, that there could be more than 24 hours (not by much but we've seen as much as a couple of minutes, and we can live with that).

 

I could figure the value out by doing some sort of calculation - I'm not sure where'd I'd put that in iReport, however.

 

Does anyone have an example, or can point me to a MySQL formatting routine, or to a tutorial somewhere that can help me with this formatting issue?

 

Thanks.

[jmurray]

You can do this in mySQL using the DATE_FORMAT and SEC_TO_TIME functions.

 

 

To convert you millisecond values to a time value you would use SEC_TO_TIME(yourmillisecondvalue/1000). That gives you a date/time value.

 

 

You can now format the output as string using DATE_FORMAT around the outside of that conversion, like this:

 

 

select CONCAT(TIME_FORMAT(SEC_TO_TIME(yourmillisecondvalue/1000),'%H:%i:%s'), ".", yourmillisecondvalue % 1000) as ACTTIME, otherfield, otherfield, from ....

 

 

.

Post edited by: jmurray, at: 2007/02/28 01:56

[GuyWithDogs]

jmurray wrote:

You can do this in mySQL using the DATE_FORMAT and SEC_TO_TIME functions. To convert you millisecond values to a time value you would use SEC_TO_TIME(yourmillisecondvalue/1000). That gives you a date/time value. You can now format the output as string using DATE_FORMAT around the outside of that conversion, like this: select CONCAT(TIME_FORMAT(SEC_TO_TIME(yourmillisecondvalue/1000),'%H:%i:%s'), ".", yourmillisecondvalue % 1000) as ACTTIME, otherfield, otherfield, from .... .<br><br>Post edited by: jmurray, at: 2007/02/28 01:56

 

Thanks for that. A couple of new functions I'd not seen before. There's a bit of an issue with the formatting of the milliseconds - if the remainder is less than 100, like 76, then the value that gets appended is .76 instead of .076.

 

I'll look at the methods to format the value. This looks great. Thanks again.

[Guest]

Hi, i would like to format a string to a date

i try to user dat_format but i have an error in the result!! can someone help me pleaze

[bigcalm]

Formatting date is fairly easy, just use:

 

(new SimpleDateFormat("dd/MM/yyyy")).format($F{ca_call_date})

 

set the output field to be a string.

 

To format an integer value into a duration in terms of hh:mm:ss I found much harder - what I ended up doing was making sure that my classpath could include some existing java code and then calling a custom function to do it. Code in the jasper report was...

 

(new imcl.common.lib.MobileDurationFormatter()).format($F{ca_call_duration})

 

The related code was..

 

public String format( Object object )

{

Integer duration = null;

if ( object instanceof Double )

{

Double dble = (Double) object;

duration = dble.intValue();

}

else

{

duration = (Integer) object;

 

}

 

Tracer.getTracer().println( " duration '" + duration + "'" );

 

int seconds = duration % 60;

int minutes = ( duration % 3600 ) / 60 ;

int hours = duration / 3600 ;

 

Tracer.getTracer().println( " seconds '" + seconds + "'" );

Tracer.getTracer().println( " minutes '" + minutes + "'" );

Tracer.getTracer().println( " hours '" + hours + "'" );

StringBuffer buffer = new StringBuffer( 10 );

 

buffer.append( hours < 10 ? "0" + hours : hours );

buffer.append( ":" );

buffer.append( minutes < 10 ? "0" + minutes : minutes );

buffer.append( ":" );

buffer.append( seconds < 10 ? "0" + seconds : seconds );

 

Tracer.getTracer().println( " string '" + buffer.toString() + "'" );

 

return( buffer.toString() );

}

[oliveira.vitor3]

To convert miliseconds for Hours, you have two options:

• Use SQL to convert.
• Add Library to your Jasperserver or your Java Application

In my case I've create a simple Java Lib and add to my Tomcat. Check the example of code

public class Util {    public String milisecondsToHours(int miliseconds) {        int seconds = ( miliseconds / 1000 ) % 60;        int minutes  = ( miliseconds / 60000 ) % 60;     // 60000   = 60 * 1000        int  hours    = miliseconds / 3600000;            // 3600000 = 60 * 60 * 1000        return String.format( "%02d:%02d:%02d", hours, minutes,seconds );    }}[/code]

Note: Just remember, Java has x86 and x64, and you have to compile correct architecture of your application