How to convert seconds into HHH:MM:SS in boxi

[sanketkumar.d]

We have one timing field in universe defined as type-number . In rich client we are trying to convert this field into hh:mm:ss format . We have used the formula as follows :

=ToDate(If((IsNull([Zresponse Time])) Or([Zresponse Time] = 0 )) Then ("00:00:00")
Else FormatNumber(Floor([Zresponse Time] /3600) ;"00") + ":" +
FormatNumber(Floor(Mod([Zresponse Time] ;3600)/60) ;"00") + ":" +
FormatNumber(Mod(Mod([Zresponse Time] ;3600) ;60) ;"00");"HH:mm:ss") 

but this is not supporting if the number of hours is three digit and displays errors in the rich client . Kindly guide on this .

[Legacy User]

Hello,

 

I think the basic problem is that you’re trying to convert a duration into the ‘time of day’ component of a date.  It might be better to leave your duration in the character string representation. If you want to allow for up to 999 hours, then changing your formats slightly as highlighted below should do the trick. Assuming your duration is in seconds, that is. If it’s in milliseconds (as SDM durations are held internally) then you’re still out by a factor of 1000.

 

=If((IsNull([Zresponse Time])) Or([Zresponse Time] = 0 )) Then ("000:00:00")

Else FormatNumber(Floor([Zresponse Time] /3600) ;"000") + ":" +

FormatNumber(Floor(Mod([Zresponse Time] ;3600)/60) ;"00") + ":" +

FormatNumber(Mod(Mod([Zresponse Time] ;3600) ;60) ;"00"))

 

Hope that helps!

Regards,

James

[JEROME MAYER]

Another way will be to do that with a computed field in the Boxi Universe.

Then you can write direct SQL to archive your need.

Assuming you store you duration as second (unixtimestamp)  will be something like:

 

CONVERT (varchar, , (Zresponse_Time /86400)) + ': '+ CONVERT(varchar, DATEADD(ss,Zresponse_Time, 0), 108);

 

will return the days/hours/minutes/second in the format like 02 days 10:00:20. you can of course extend from that...

 

There is many different way to archive this in TSQL and you can found many different examples on internet on how to do that.

 

My 2 cents

/J

[JEROME MAYER]

my your duration never exceed 24 hours can either simplify it taking only the last portion of it

 

CONVERT(varchar, DATEADD(ss, Zresponse_Time, 0), 108)

 

/J

[Legacy User]

Hello,

i think the first approach was correct. I would create a function and give back the required value:

e.g.

IF Zresponse Time> 0 THEN
RETURN (CASE WHEN Zresponse Time> 0 THEN
                TRIM(to_char(TRUNC(Zresponse Time/ 3600, 0), '9999909')) || ':'
                || TRIM(to_char(TRUNC(MOD(Zresponse Time, 3600) / 60, 0), '09')) || ':'
                || TRIM(to_char(TRUNC(Zresponse Time-TRUNC(Zresponse Time/ 3600, 0) *3600 -TRUNC(MOD(Zresponse Time, 3600) / 60, 0) *60, 0), '09'))
END);
ELSE
RETURN '00:00:00';
END IF;

 

Hope that helps.
best regards, Besim