Average start time of an overnight job

Problem:

You have an overnight job which is triggered by the arrival of some external data.  You keep a record of the date and time that the overnight job is run.   Someone wants to know what the average start time of the job is.  You can’t use the average function on dates and times in T-SQL so you need to write some custom code to calculate the average time the job starts.

Solution:

First, you need to identify the point in time that you want the calculation to consider the start of a new day.  For a job which runs during the day could be 0 – indicating midnight.  For an overnight job you would probably want to consider the possibility that the job could be started the next morning.  In that situation you might choose 18 – indicating 6pm as the start of a new day.

Then, in the calculation itself:

  1. identify the number of seconds since the day division
  2. get the average of number of seconds
  3. add to a date time and extract the time portion

-- Defines the hour of the day when a new day starts
DECLARE @DayDivision INT = 12

IF OBJECT_ID(N'tempdb..#StartTimes') IS NOT NULL DROP TABLE #StartTimes

CREATE TABLE #StartTimes(
	start DATETIME NOT NULL
)

INSERT INTO #StartTimes
VALUES 
	 ('2018-01-09 00:01:38.000')
	,('2018-01-09 23:43:22.000')

SELECT 
	-- 3. Add the number of seconds to a day starting at the 
	--    day division hour, then extract the time portion
	CAST(DATEADD(SECOND,
		-- 2. Average number of seconds
		AVG(
			-- 1. Get the number of seconds from the day division point (@DayDivision)
			DATEDIFF(SECOND, 
				CASE WHEN DATEPART(HOUR,start) < @DayDivision THEN
					SMALLDATETIMEFROMPARTS(YEAR(DATEADD(DAY,-1,start)),MONTH(DATEADD(DAY,-1,start)),DAY(DATEADD(DAY,-1,start)),@DayDivision,0)
				ELSE
					SMALLDATETIMEFROMPARTS(YEAR(start),MONTH(start),DAY(start),@DayDivision,0)
				END
			,start)
		)
	,'01 jan 1900 ' + CAST(@DayDivision AS VARCHAR(2)) + ':00') AS TIME) AS AverageStartTime
FROM #StartTimes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s