July 29, 2008 at 6:49 am
Hi all
I recently decided to start to report on durations of jobs that I have scheduled on my SQL 2005 system. Rather than reinvent the wheel I thought a script would have been written and posted on numerous forums.
After a few searches I found many different ways to pull the information I needed from the SYSJOBS and SYSJOBHISTORY tables. Most scripts created the dates required using various string manipulation methods.
One script caught my eye that seemed more elegant than most:
http://www.dbforums.com/archive/index.php/t-630647.html">
http://www.dbforums.com/archive/index.php/t-630647.html
select
convert(datetime,rtrim(run_date))
+ (run_time*9+run_time%10000*6+run_time%100*10+25*du ration)/216e4
from msdb..sysjobhistory
While this script gave me the result I required it caused more questions.
When converting an INT field (Run_Date) to DATETIME one requires a RTRIM otherwise the conversion produces an error?
How does the above formula work? I understand the modulo (%) parts of the calculation being used to find the minutes and seconds part of the run_time field but why multiply them by 6 and 10?
Why would you multiply run_time by 9 at the start of the calculation?
Why would you multiply run_duration by 25?
What’s the significance of the 216e4 (2160000) value? I suspect the calculation is trying to calculate the total run time value in milliseconds perhaps, so is the 216e4 value supposed to be the number of milliseconds in a day? Surely that should be 1000*60*60*24 = 864e5 (86400000)?
Any pointers as to how the above formula works would be greatly appreciated.
Many thanks.
Nick.
August 5, 2008 at 7:44 am
So anyone have any thoughts on the above post?
August 6, 2008 at 9:47 am
Can you please clarify what your after? Are you trying to get a report of start and end times? Average durations? What?
Thanks,
MentalWhiteNoise
August 8, 2008 at 7:24 am
Hi
Thanks for the reply.
The Formula is supposed to produce a date and time of how long the job took to run using the run_date, run_time and duration fields.
August 8, 2008 at 10:27 am
Okay, I am having trouble understanding the why of your reference code as well, so here is what I did:
SELECT run_time
, run_time/10000 AS run_time_hours
, (run_time%10000)/100 AS run_time_minutes
, (run_time%10000)%100 AS run_time_seconds
, (run_time/10000 /*run_time_hours*/ * 60 * 60 /* hours to minutes to seconds*/)
+ ((run_time%10000)/100 /* run_time_minutes */ * 60 /* minutes to seconds */ )
+ (run_time%10000)%100 AS run_time_elapsed_seconds
, CONVERT(DATETIME, RTRIM(run_date)) AS Start_Date
, CONVERT(DATETIME, RTRIM(run_date)) +
((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100 /*run_time_elapsed_seconds*/) / (23.999999*3600 /* seconds in a day*/) AS Start_DateTime
, ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/)
, CONVERT(DATETIME, RTRIM(run_date)) + ((run_time/10000 * 3600) + ((run_time%10000)/100*60) + (run_time%10000)%100) / (86399.9964 /* Start Date Time */)
+ ((run_duration/10000 * 3600) + ((run_duration%10000)/100*60) + (run_duration%10000)%100 /*run_duration_elapsed_seconds*/) / (86399.9964 /* seconds in a day*/) AS End_DateTime
FROM msdb.dbo.sysjobhistory
Hopefully this shows my work.
FYI, the 23.999999 is required to get to the correct precision of seconds.
After doing this, I am getting a factor of 2.777E-4 versus a factor of 2.893E-4 in your current code. This seems to me to be a difference of rounding. The one thing worth noting is that my solution involves taking the modulus of a modulus, versus the other solution which does not.
While I know this doesn't answer your question, I hope this helps get to an efficient solution!
August 9, 2008 at 2:54 am
DECLARE@d INT,
@t INT
SELECT@d = 20080504,-- May 4, 2008
@t = 42412-- 4:24:12 AM
SELECTCAST(STR(@d, 8, 0) AS DATETIME),
CAST(STUFF(STUFF(STR(@t, 6, 0), 3, 0, ':'), 6, 0, ':') AS DATETIME)
N 56°04'39.16"
E 12°55'05.25"
August 11, 2008 at 5:05 am
Many thanks for the reply’s.
MentalWhiteNoise and Peso:
I have used both these methods to get the data that I require.
What I was after in this post was to try and work out how the example I posted worked.
After searching through various forums I found a number of ways to calculate the dates and time I required. I came across the example code that I posted on various forums and just couldn't work out how it was supposed to work.
September 4, 2008 at 10:44 am
nplace6530 (7/29/2008)
Hi allI recently decided to start to report on durations of jobs that I have scheduled on my SQL 2005 system. Rather than reinvent the wheel I thought a script would have been written and posted on numerous forums.
After a few searches I found many different ways to pull the information I needed from the SYSJOBS and SYSJOBHISTORY tables. Most scripts created the dates required using various string manipulation methods.
One script caught my eye that seemed more elegant than most:
http://www.dbforums.com/archive/index.php/t-630647.html">
http://www.dbforums.com/archive/index.php/t-630647.html
select
convert(datetime,rtrim(run_date))
+ (run_time*9+run_time%10000*6+run_time%100*10+25*du ration)/216e4
from msdb..sysjobhistory
While this script gave me the result I required it caused more questions.
When converting an INT field (Run_Date) to DATETIME one requires a RTRIM otherwise the conversion produces an error?
How does the above formula work? I understand the modulo (%) parts of the calculation being used to find the minutes and seconds part of the run_time field but why multiply them by 6 and 10?
Why would you multiply run_time by 9 at the start of the calculation?
Why would you multiply run_duration by 25?
What’s the significance of the 216e4 (2160000) value? I suspect the calculation is trying to calculate the total run time value in milliseconds perhaps, so is the 216e4 value supposed to be the number of milliseconds in a day? Surely that should be 1000*60*60*24 = 864e5 (86400000)?
Any pointers as to how the above formula works would be greatly appreciated.
Many thanks.
Nick.
Basically, whoever wrote this query was on a mission to use as few characters as possible.
When converting an INT field (Run_Date) to DATETIME one requires a RTRIM otherwise the conversion produces an error?
convert(datetime,rtrim(run_date)) would be written more appropriately as:
convert(datetime,cast(run_date as varchar))
you can't convert from int to datetime. you must first convert from int to varchar, then varchar to datetime. rather than explicitly converting from int to varchar, the author uses the rtrim function. rtrim removes empty white space from the end of a string, which doesnt even exist here.. rtrim expects a varchar, but when it is passed an int, it implicitly converts from int to varchar. so the author isn't using rtrim to actually trim anything, but for the implicit conversion. it's very unclear, but i guess it uses less letters than cast as varchar.
field but why multiply them by 6 and 10?
Why would you multiply run_time by 9 at the start of the calculation?
Why would you multiply run_duration by 25?
What’s the significance of the 216e4 (2160000) value?
So the author is multiplying the whole number by 9, the last 4 digits by 6, and the last 2 digits by 10...
in essence, the author is multiplying the first 2 digits by 90000 (9 x 10000), the middle 2 digits by 1500 ((9 + 6) x 100) and the last 2 digits by 25 ((9 + 6 + 10) x 1)
The ratio 90000:1500:25 is the same ratio as 3600:60:1... just multiplied by 25.
The author could have replaced 9 with .3600, 6 with .24 (.60 - .36) and 10 with .4 (1 - .6).
If the author did that, then the result would only have to be divided by 84600 instead of 216e4 (25 times 86400)(btw... 86400 is 3600 seconds in an hour x 24 hours in a day)
so,
(run_time*9+run_time%10000*6+run_time%100*10+25*run_duration)/216e4 could be rewritten as:
(run_time*.36+run_time%10000*.24+run_time%100*.4+run_duration)/86400
the problem is that:
(run_time*.36+run_time%10000*.24+run_time%100*.4+run_duration)/86400
doesnt yield as many significant digits, so you would have to cast all of the run_time modulo expressions as floats as such:
(cast(run_time as float)*.36+cast(run_time%10000 as float)*.24+cast(run_time%100 as float)*.4+run_duration)/86400
All in all, here is a more clearly query, which produces the same result:
select
convert(datetime,cast(run_date as varchar))
+(cast(run_time as float)*(.36)+cast(run_time%10000 as float)*(.6-.36)+cast(run_time%100 as float)*(1-.6)+run_duration)/86400
from msdb..sysjobhistory
But again, the author is using as few characters as possible...
Hopefully that breaks it down enough for you.
-Mike
January 13, 2009 at 1:49 pm
I've been chasing this issue as well, and stumbled across the same code that the original poster found. Unfortunately, that code, which I traced back as early as 2002, does not appear to work... at least not in SQL 2005.
Given a run_date of 20090112, a run_time of 190239 and a run_duration of 2210, you'd expect the code in the original post to produce 2009-01-12 19:24:49.000. Instead it produces 2009-01-12 19:39:29.000, which is exactly what you'd get when you add 2,210 seconds to a time instead of adding 22 minutes and 10 seconds. I consulted BOL and sure enough, run_duration is an integer representation of time in HHMMSS format, and not an integer containing the number of seconds it took for a job to run.
Here's the code I originally found (link):
endTime = DATEADD
(
SECOND,
jh.run_duration,
CAST
(
CONVERT(VARCHAR, jh.run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')
AS DATETIME
)
)
By my eye it's a little easier to read than the code from the original post, although you'll see that the link includes that code as well. This code has the same flaw the the code from the OP does as well.
Here's my modification. For formatting style isn't what I use, but I wanted to keep this as close as possible to the original post.
endTime = CAST
(
CONVERT(VARCHAR, jh.run_date)
+ ' ' + STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,jh.run_time),6),5,0,':'),3,0,':')
AS DATETIME) +
STUFF(STUFF(RIGHT('000000'
+ CONVERT(VARCHAR,jh.run_duration),6),5,0,':'),3,0,':')
Maybe I've got this whole thing wrong and I've simply overlooked something, but it honestly appears to me that the same example code we've all been using is just flat-out wrong.
Edit: Replaced "history." with "jh." in my example to align with rest of code.
January 13, 2009 at 2:56 pm
Will not work for "small" times, as 115 (00:01:15)
Use this
DECLARE@rundate INT,
@runtime INT,
@duration INT
SELECT@rundate = 20080504,-- May 4, 2008
@runtime = 42412,-- 4:24:12 AM,
@duration = 115-- 00:01:15
SELECTrundate,
runtime,
duration,
rundate + runtime AS start,
rundate + runtime + duration AS finish
FROM(
SELECTCAST(STR(@rundate, 8, 0) AS DATETIME) AS rundate,
CAST(STUFF(STUFF(REPLACE(STR(@runtime, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS runtime,
CAST(STUFF(STUFF(REPLACE(STR(@duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS DATETIME) AS duration
) AS d
N 56°04'39.16"
E 12°55'05.25"
January 13, 2009 at 4:45 pm
I admit I wrote some pretty bad code and forgot to replace "history." with "jh.", but that's been fixed. During the testing process I did a little edge testing and the code worked just fine. I plugged your example of "115" into my code and it correctly added 1 minute, 15 seconds to the start time. I'm not sure why you received different results.
June 13, 2010 at 4:38 am
Or you could just forget doing all the formatting yourself and use the msdb sys function dbo.agent_datetime(@date int no default,@time int no default)
so to get the run date and time
SELECT dbo.agent_datetime(run_date,run_time)
or to get the completed date time
SELECT DATEADD(s,DATEDIFF(s,dbo.agent_datetime(run_date,0),dbo.agent_datetime(run_date,run_duration)),dbo.agent_datetime(run_date,run_time))
March 25, 2012 at 7:38 am
thanks, probably the best working code dealing with the subject. I replaced the datetime with time(0), just easier on my eyes:
CAST(STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS runtime,
CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS duration
March 26, 2012 at 7:30 am
BitWise MnM (3/25/2012)
thanks, probably the best working code dealing with the subject. I replaced the datetime with time(0), just easier on my eyes:CAST(STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS runtime,
CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS time(0)) AS duration
It's probably worth mentioning that TIME(0) was added to T-SQL in SQL Server 2008.
November 19, 2012 at 2:53 am
Thanks a lot Timothy ....really helped me a lot ... Really appreciated it
Regards,
Bharath
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply