PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 08/09/2016 8:22 AM by  TBonney
Convert job duration from decimal to mm:ss
 4 Replies
Sort:
You are not authorized to post a reply.
Author Messages
TBonney
Private
Private
Veteran Member
(570 points)
Veteran Member
Posts:240


Send Message:

--
08/01/2016 12: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
    Private
    Private
    Basic Member
    (30 points)
    Basic Member
    Posts:10


    Send Message:

    --
    08/03/2016 2: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-helper.com/ti...rmats.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
    Private
    Private
    Veteran Member
    (570 points)
    Veteran Member
    Posts:240


    Send Message:

    --
    08/04/2016 9:32 AM
    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
    Private
    Private
    Veteran Member
    (3873 points)
    Veteran Member
    Posts:1377


    Send Message:

    --
    08/08/2016 4: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
    Private
    Private
    Veteran Member
    (570 points)
    Veteran Member
    Posts:240


    Send Message:

    --
    08/09/2016 8:22 AM
    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
    You are not authorized to post a reply.