What's taking so long? Querying msdb.dbo.sysjobhistory on SQL Server

I recently had a client ask me to provide the average runtimes of individual SQL Agent job steps. This can be trickier than you’d think because the run_duration column does not represent the number of seconds the job ran. For example, a value of 12345 does not represent 3 hours and 43 minutes. Instead, it is showing 1 hour, 23 minutes, and 45 seconds (1:23:45). A similar problem exists when trying to display the date and time the step ran. Although the run_date column can easily be seen as YYYYMMDD (20240501 = May 1, 2024), the run_time is not the number of seconds after midnight or some other calculation. It is really a numeric equivalent of HHMMSS. For example, 134151 = 13:41:51 (or 1:41:51PM on a 12-hour clock). Fortunately, there is a built-in function to convert run_date and run_time to datetime called dbo.agent_datetime which accepts two parameters (run_date, run_time). Unfortunately, the same does not exist for converting run_duration to seconds, or a readable format.

Using documentation found at https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobhistory-transact-sql?view=sql-server-ver16 I created two functions to return the duration in seconds and also in a readable (d.HH:MM:SS) format.

The following are the create scripts and example query.

USE msdb;
GO

CREATE FUNCTION dbo.agent_duration (@run_duration INT)
RETURNS VARCHAR(12)
AS
    BEGIN
        DECLARE @ConvertedRunDuration VARCHAR(10);
        SET @ConvertedRunDuration
            = CASE
                  WHEN @run_duration > 235959 THEN
                      CAST((CAST(LEFT(CAST(@run_duration AS VARCHAR), LEN(CAST(@run_duration AS VARCHAR)) - 4) AS INT)
                            / 24) AS VARCHAR) + '.'
                      + RIGHT('00'
                              + CAST(CAST(LEFT(CAST(@run_duration AS VARCHAR), LEN(CAST(@run_duration AS VARCHAR)) - 4) AS INT)
                                     % 24 AS VARCHAR), 2) + ':'
                      + STUFF(CAST(RIGHT(CAST(@run_duration AS VARCHAR), 4) AS VARCHAR(6)), 3, 0, ':')
                  ELSE
                      STUFF(
                          STUFF(RIGHT(REPLICATE('0', 6) + CAST(@run_duration AS VARCHAR(6)), 6), 3, 0, ':'), 6, 0, ':')
              END;
        RETURN @ConvertedRunDuration;
    END;
GO

CREATE FUNCTION dbo.agent_duration_seconds (@run_duration INT)
RETURNS INT
AS
    BEGIN
        DECLARE @ConvertedRunDurationSeconds INT;
        SET @ConvertedRunDurationSeconds
            = (@run_duration / 10000) * 3600 -- convert hours to seconds, can be greater than 24
              + ((@run_duration % 10000) / 100) * 60 -- convert minutes to seconds
              + (@run_duration % 100);
        RETURN @ConvertedRunDurationSeconds;
    END;
GO

/*Example query*/

SELECT sjh.run_date,
       sjh.run_time,
       sjh.run_duration,
       [Start Date/Time] = msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time),
       [End Date/Time] = DATEADD(
                             SECOND,
                             msdb.dbo.agent_duration_seconds(sjh.run_duration),
                             msdb.dbo.agent_datetime(sjh.run_date, sjh.run_time)),
       [Formatted Duration (d.HH:MM:SS)] = msdb.dbo.agent_duration(sjh.run_duration),
       [Duration in Seconds] = msdb.dbo.agent_duration_seconds(sjh.run_duration)
FROM msdb.dbo.sysjobhistory AS sjh;        

?The output should look something like this:

Back to the original ask - "what is the average run time per step per job?" Try the following. Note: I removed the job and step names for privacy reasons. Let me know if this works for you. Did I miss something? Is there an easier way?

SELECT sj.job_id,
       sjh.step_id,
       [Avg Duration (seconds)] = AVG(msdb.dbo.agent_duration_seconds(sjh.run_duration))
FROM msdb.dbo.sysjobhistory AS sjh
    INNER JOIN msdb.dbo.sysjobs AS sj ON sj.job_id = sjh.job_id
WHERE sjh.step_id <> 0
      AND sjh.run_status = 1
GROUP BY sj.job_id,
         sjh.step_id;