Evaluation of run time in QUEUEDJOB table

TBonney

Veteran Member

Posts: 277

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

Veteran Member

Posts: 1498

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

Veteran Member

Posts: 277

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

Veteran Member

Posts: 1498

3/21/2022 5:34 PM

You are welcome!

Glad I was able to help out.

LarryD

Basic Member

Posts: 7

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 ;