PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 4/2/2022 8:33 PM by  LarryD
Evaluation of run time in QUEUEDJOB table
 4 Replies
Sort:
You are not authorized to post a reply.
Author Messages
TBonney
Private
Private
Veteran Member
(640 points)
Veteran Member
Posts:276


Send Message:

--
3/21/2022 3:53 PM

    For use within SQL queries, how do folks evaluate the total run time of jobs in the QUEUEDJOB table, since the STARTTIME & STOPTIME fields are formatted as (decimal(6,0),not null), instead of date/time format?

    We are trying to write a query to identify "long-running" jobs, in the active job queue by comparing the STARTTIME to GETDATE() tme stamp, but not sure how to convert the STARTDATE formatting in order to be able to successfully compare the two.

    Any advice would be appreciated. Thank you in advance.

    Greg Moeller
    Private
    Private
    Veteran Member
    (4123 points)
    Veteran Member
    Posts:1471


    Send Message:

    --
    3/21/2022 4:09 PM
    I'm not sure who gave me this query, so I can't give proper credit.. but here's what we've used:
    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 gen.dbo.QUEUEDJOB
    WHERE STATUS = '63' AND CONVERT(VARCHAR(21),STOPDATE,101) = CONVERT(VARCHAR(21),GETDATE(),101)
    -- ORDER BY 'RunTime Duration' desc
    ORDER BY ACTSTARTDATE desc, ACTSTARTTIME desc
    TBonney
    Private
    Private
    Veteran Member
    (640 points)
    Veteran Member
    Posts:276


    Send Message:

    --
    3/21/2022 5:27 PM
    Greg,

    Thanks so much. Although you say you can't give proper credit to the original author, I can certainly give you the credit. Thanks for the help! That 'Run Time Duration' column is exactly what I was looking for.
    Greg Moeller
    Private
    Private
    Veteran Member
    (4123 points)
    Veteran Member
    Posts:1471


    Send Message:

    --
    3/21/2022 5:34 PM

    You are welcome!

    Glad I was able to help out.

    LarryD
    APPLICATIONS DEVELOPER
    Private
    (9 points)
    Posts:3


    Send Message:

    --
    4/2/2022 8:33 PM

    In case anyone is interested in an Oracle solution to this issue. The first trick is to convert the DATE and TIME columns to character strings, concatenate them, then convert them back to dates before subtracting them to get the duration. I had this part a few years ago. The problem was converting the result back to a character string that had just the hours, minutes, seconds. I received various errors depending on what I tried.
    I then found an example on the Internet where the 'Epoch time' of Jan 1 1970 was added to the difference.
    Since Oracle stores DATE datatypes as the number of seconds since epoch, adding that date is essentially adding zero. However, this seems to perform an implicit converstion of the result to the character format used for the epoch value. Just make sure that its time portion contains the format that you want in your final result. Greg's solution uses the MSSQL Date style of 114. If you want fractional seconds like it has, in Oracle you will need to convert to TIMESTAMP vs DATE.

    SELECT USERNAME, JOBNAME, TOKEN, ACTSTARTDATE, ACTSTARTTIME, STOPDATE, STOPTIME,
    to_char(
    to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss' +
    ( to_date(to_char(STOPDATE,'mmddyyyy'||to_char(STOPTIME,'099999','mmddyyyyhh24miss'
    - to_date(to_char(ACTSTARTDATE,'mmddyyyy'||(to_char(ACTSTARTTIME,'099999' ),'mmddyyyyhh24miss'
    )
    ,'hh24:mi:ss' as "RunTime Duration"
    from gen.queuedjob
    --your optional WHERE clause here
    order by actstartdate desc ;

    You are not authorized to post a reply.