Prev
Next
Forums Infor / Lawson Platforms S3 Systems Administration

Convert job duration from decimal to mm:ss

 4 Replies
 1 Subscribed to this topic
 27 Subscribed to this forum
Sort:
Author
Messages
TBonney
Veteran Member
Posts: 281
Veteran Member
8/1/2016 4:55 PM

    Does anyone do any kind of duration computations on job run times from the (GEN) QUEUEDJOB table, based on the difference between the start & stop times?

    I'd like to do something like this, by taking the difference between the stop time and the starttime or actstarttime, but since these fields are in decimal format instead of time format, I am having trouble determining how to convert them to minutes & seconds. (Since it is job run times I am trying to convert, I shouldn't ever need to go as far out as hours, so would only need MM:SS)

    Has anybody ever done this and would you be willing to share how? Thank you.

    Erik
    Basic Member
    Posts: 10
    Basic Member
    8/3/2016 6:56 PM
      If you are referring to a SQL/Oracle query then you'll probably need use the appropriate "convert" command for the syntax you're looking for.

      http://www.sql-server-hel...ps/date-formats.aspx

       

      If by decimal format you mean it's counting the number of seconds you could subtract the start number from the end number and then convert seconds into into mm:ss format, although I'm not sure how to do this except in Crystal since formulas are harder to do with a database query.

      TBonney
      Veteran Member
      Posts: 281
      Veteran Member
      8/4/2016 1:32 PM
        So, I've gotten to the point where I can determine the difference between the two, but only in a total number of seconds. Anyone kjnow how I can now take this a step further and convert the result to hh:mm:ss to be presented to the end users? Following is the current query I am using:

        SELECT USERNAME,JOBNAME,TOKEN,ACTSTARTDATE,ACTSTARTTIME,
        CAST(DATEADD(SECOND, FLOOR(ACTSTARTTIME / 10000) * 3600 + FLOOR(ACTSTARTTIME / 100) % 100 * 60 + ACTSTARTTIME % 100, 0) as time) as 'Start HH:mm:ss',
        STOPTIME,
        CAST(DATEADD(SECOND, FLOOR(STOPTIME / 10000) * 3600 + FLOOR(STOPTIME / 100) % 100 * 60 + STOPTIME % 100, 0) as time) as 'Stop HH:mm:ss',
        DATEDIFF(SECOND,CAST(DATEADD(SECOND, FLOOR(ACTSTARTTIME / 10000) * 3600 + FLOOR(ACTSTARTTIME / 100) % 100 * 60 + ACTSTARTTIME % 100, 0) as time),
        CAST(DATEADD(SECOND, FLOOR(STOPTIME / 10000) * 3600 + FLOOR(STOPTIME / 100) % 100 * 60 + STOPTIME % 100, 0) as time)) AS 'Duration (in Seconds)'
        FROM lsfprod_gen.dbo.QUEUEDJOB
        WHERE ACTSTARTDATE = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
        ORDER BY 'Duration (in Seconds)' desc

        Thanks for any insight you may provide!
        Greg Moeller
        Veteran Member
        Posts: 1498
        Veteran Member
        8/8/2016 8:19 PM
          I found this, but I'm not well enough versed in sql code to modify yours...
          declare @seconds int
          set @seconds = 241 -- (Your result)
          select convert(char(8), dateadd(second, @seconds, ''), 114) as f_time
          TBonney
          Veteran Member
          Posts: 281
          Veteran Member
          8/9/2016 12:22 PM
            Thanks Greg...All I ended up needing/using was your date conversion type of 114 and applying it to my already existing syntax for grabbing the run time in total number of seconds. I appreciate the help!!

            I realize that there is likely a more efficient approach. However, for anyone who may be in the same boat I was in, this is how I ended up accomplishing this:

            SELECT USERNAME,JOBNAME,TOKEN,ACTSTARTDATE,ACTSTARTTIME,
            CAST(DATEADD(SECOND, FLOOR(ACTSTARTTIME / 10000) * 3600 + FLOOR(ACTSTARTTIME / 100) % 100 * 60 + ACTSTARTTIME % 100, 0) as time) as 'Start HH:mm:ss',
            STOPTIME,
            CAST(DATEADD(SECOND, FLOOR(STOPTIME / 10000) * 3600 + FLOOR(STOPTIME / 100) % 100 * 60 + STOPTIME % 100, 0) as time) as 'Stop HH:mm:ss',
            CONVERT(CHAR(8), DATEADD(SECOND, DATEDIFF(SECOND,CAST(DATEADD(SECOND, FLOOR(ACTSTARTTIME / 10000) * 3600 + FLOOR(ACTSTARTTIME / 100) % 100 * 60 + ACTSTARTTIME % 100, 0) AS TIME), CAST(DATEADD(SECOND, FLOOR(STOPTIME / 10000) * 3600 + FLOOR(STOPTIME / 100) % 100 * 60 + STOPTIME % 100, 0) AS TIME)), ''), 114) AS 'RunTime Duration'
            FROM lsfprod_gen.dbo.QUEUEDJOB
            WHERE STATUS = '63' AND CONVERT(VARCHAR(21),STOPDATE,101) = CONVERT(VARCHAR(21),GETDATE(),101)
            ORDER BY 'RunTime Duration' desc