Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Working with SQL Agent Durations Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 4:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:14 AM
Points: 21, Visits: 91
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
Posted Thursday, August 23, 2012 10:41 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 21, 2014 4:44 AM
Points: 44, Visits: 237
Yo Dude!

Great post. Thanks for sharing!
Post #1349452
Posted Friday, August 24, 2012 2:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:25 AM
Points: 197, Visits: 1,253
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;

Post #1349918
Posted Friday, August 24, 2012 3:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:44 AM
Points: 194, Visits: 1,142
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
Posted Saturday, August 25, 2012 7:12 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
Kyle Neier , (8/24/2012)
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!

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
Posted Thursday, August 30, 2012 4:25 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:18 AM
Points: 5, Visits: 309
+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
Posted Tuesday, December 25, 2012 8:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-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 »

Add to briefcase ««12

Permissions Expand / Collapse