Convert job duration from decimal to mm:ss

Name	Points
Greg Moeller	4184
David Williams	3349
JonA	3291
Kat V	2984
Woozy	1973
Jimmy Chiu	1883
Kwane McNeal	1437
Ragu Raghavan	1375
Roger French	1315
mark.cook	1244

TBonney

Veteran Member Send Private Message

Posts: 281

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 Send Private Message

Posts: 10

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 Send Private Message

Posts: 281

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 Send Private Message

Posts: 1498

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 Send Private Message

Posts: 281

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