CA Service Management

Expand all | Collapse all

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

  • 1.  How to convert seconds into HHH:MM:SS in boxi

    Posted 08-24-2016 09:39 AM

    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 .



  • 2.  Re: How to convert seconds into HHH:MM:SS in boxi

    Broadcom Employee
    Posted 08-24-2016 09:56 PM

    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



  • 3.  Re: How to convert seconds into HHH:MM:SS in boxi

    Posted 08-30-2016 05:35 AM

    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



  • 4.  Re: How to convert seconds into HHH:MM:SS in boxi

    Posted 08-30-2016 05:41 AM

    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



  • 5.  Re: How to convert seconds into HHH:MM:SS in boxi

    Posted 08-30-2016 06:43 AM

    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