Working with SQL Agent Durations

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Kyle Neier » Working with SQL Agent Durations

17 posts, Page 2 of 2

««1 2

Working with SQL Agent Durations

Rate Topic

Display Mode

Topic Options

Author

Message

intrope

Posted Thursday, August 23, 2012 4:09 PM

Grasshopper

Group: General Forum Members
Last Login: Thursday, March 28, 2013 12:41 PM
Points: 21, Visits: 77

You can actually avoid using trunctation. Consider that if @t is the sql agent time, @t % 100 will be seconds, @t % 10000 - @t % 100 will be minutes * 100, and @t - @t % 10000 will be hours * 10000. So, the subsequent division by 100 or 10000 is already an integer value.

DECLARE @t int = 1234556; --4546 --5; --56; --1234556;

SELECT 
	@t AS AgentDuration,
	(@t - @t % 10000) /10000 AS hours,
	(@t % 10000 - @t % 100) /100 AS minutes,	
	@t % 100 AS SECONDS,

	((@t - @t % 10000) /10000) * 60 * 60 
	+ ((@t % 10000 - @t % 100) /100) * 60
	+ @t % 100 AS totalseconds

Added bonus: it'll work for negative values too. Not that output will mean anything (GIGO!)

Post #1349383

Cyclone

Posted Thursday, August 23, 2012 10:41 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 03, 2013 3:40 AM
Points: 42, Visits: 225

Yo Dude!

Great post. Thanks for sharing!
Smile

Post #1349452

Carolyn Sellers

Posted Friday, August 24, 2012 2:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:30 PM
Points: 173, Visits: 910

Dear All,

If running script in SQL 2005 or greater, you can save yourself some time by using msdb system function agent_datetime. Check out this thread http://www.sqlservercentral.com/Forums/Topic542581-145-2.aspx.

Give this script a try;

select [step_name]
,run_duration
,run_date
,msdb.dbo.agent_datetime(run_date,run_time) as StartDateTime
,dateadd(s,datediff(s,msdb.dbo.agent_datetime(run_date,0),msdb.dbo.agent_datetime(run_date,run_duration))
,msdb.dbo.agent_datetime(run_date,run_time)) as EndDateTime
from msdb.dbo.sysjobhistory
where datediff(hh, msdb.dbo.agent_datetime(run_date,run_time), getdate()) <= 240
order by StartDateTime desc;

Hehe

Post #1349918

Kyle Neier ,

Posted Friday, August 24, 2012 3:46 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 5:57 AM
Points: 194, Visits: 1,060

It seems that my article about Powershell has sparked some T-SQL discussion, so I decided to take the prompt and run some performance analysis on the different solutions presented here.

You can read the detail over on my blog (http://sqldbamusings.blogspot.com/2012/08/t-sql-performance-of-sql-agent-duration.html), but essentially, the conversions that use math instead of string manipulation seem to be faster and cover a wider range of potential values.

Before I ran the tests, I really had no clue what the results would be, which is why I wanted to run them. Thanks to all of you the great suggestions - another testament to how great the SQL community is!

Post #1349957

rmechaber

Posted Saturday, August 25, 2012 7:12 AM

SSChasing Mays

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 7:43 AM
Points: 656, Visits: 2,921

Kyle Neier , (8/24/2012)

Well, that is interesting.

Carolyn, I wasn't aware of that msdb sys function, so thanks for pointing it out.

That said, I was pretty surprised to find that my hack string manipulation performed 7X faster than the msdb function agent_datetime. Thanks for testing it!

I suppose if I were running a lot of code against a very large table of job histories, I might go back and tweak my code. I suspect that most DBAs running code against msdb.dbo.sysjobhistory are doing it for similar reasons though: a once-a-day (or thereabouts) system check and reporting. Still, I prefer your approach, both for performance and pedagogically: why deal with times as strings?

Kyle, have you seen any documentation anywhere as to why MS has coded times this way?

Thanks
Rich
Thanks for

Post #1350039

OldFashionGang

Posted Thursday, August 30, 2012 4:25 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 3:42 AM
Points: 3, Visits: 195

+5 cent's of mine

select top 100
name 
, 
convert(datetime,(convert(varchar(8),h.run_date)))
+(
+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),1,2)
+':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),3,2)
+':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),5,2)
)
begining 
,
convert (varchar ,convert(int,reverse (substring (reverse (convert (varchar , run_duration)),5,4))))+
+':'+substring (right( '000000'+(convert (varchar , run_duration) ),6),3,2)
+':'+substring (right( '000000'+(convert (varchar , run_duration) ),6),5,2)
duration
, 
-- EdsTime as begining+duration
convert( datetime, convert(varchar(8),h.run_date)) 
+ (
       substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),1,2)
  +':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),3,2)
  +':'+substring (right( '000000'+(convert (varchar (6), h.run_time) ),6),5,2)
  )
+

  (  -- add hours
    dateadd (hh, convert(int,reverse (substring (reverse (convert (varchar , run_duration)),5,4))
    ), 0)
  + 
    -- add mins
    dateadd (mi, convert (int, 
           substring (right( '000000'+(convert (varchar , run_duration) ),6),3,2)
    ), 0)
  + 
    -- add seconds
    dateadd (ss, convert (int, 
    substring (right( '000000'+(convert (varchar , run_duration) ),6),5,2)
    ), 0)
  ) 
endtime 
from msdb..sysjobhistory h with( nolock), msdb..sysjobs j  with( nolock)
where h.step_id=0 and h.job_id=j.job_id order by instance_id desc

works for several years to make this:

Post #1352089

Neha05

Posted Tuesday, December 25, 2012 8:46 AM

SSC-Addicted

Group: General Forum Members
Last Login: Monday, April 15, 2013 11:42 PM
Points: 424, Visits: 55

Good article.

Post #1400079

« Prev Topic | Next Topic »

17 posts, Page 2 of 2

««1 2

Permissions