home

-- TSQL - Date arithmetic, average duration
Author Nigel Rivett

The requirement is to calculate the average duration from start and end datetimes
This discusses methods of obtaining this result and methods of performing arrithmetic on datetimes in general

--This will calculate the duration as a datetime for each entry
declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'

;with dur as
(
select dur = datediff(ss,Time_Start, Time_end)
from @t
)
select hh = dur.dur/60/60, mm = dur.dur/60%60, ss = dur.dur%60
from dur

-- This might appear to work to give the average
;with dur as
(select dur = datediff(ss,Time_Start, Time_end)
from @t
)
select hh = avg(dur.dur)/60/60, mm = avg(dur.dur)/60%60, ss = avg(dur.dur)%60
from dur
/*
That's fine but it relies on the result of the datediff which gives an integer
*/
Try
declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '18000101 00:00:00', '20000102 00:00:01', 'Y'

;with dur as
(
select dur = datediff(ss,Time_Start, Time_end )
from @t
)
select dur.dur/60/60, dur.dur/60%60, dur.dur%60
from dur

Msg 535, Level 16, State 0, Line 33
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large.

Try to use datediff with a less precise datepart.
Note: From v2016 there is a function datediff_big which will allow this to work

We can use the sql server datetime representation to perform this operation.
A datetime is held in two parts. The first part is the number of days from 19000101.
The second part is the time.
I like to think of this as a decimal number with the integer part being the number of days.
This can be used for arithmetic and is why addition of days works
select getdate()+2
is equivalent to

select convert(datetime,0.5)
gives 19000101 + 0.5 days - i.e midday on that day.

So we can transform a datetime to numeric datatypes. I usually use a float.
declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '18000101 00:00:00', '20000102 00:00:01', 'Y'

select Time_Start = convert(float,Time_Start), Time_end = convert(float,Time_end) from @t
select Time_Start = convert(datetime,convert(float,Time_Start)), Time_end = convert(datetime,convert(float,Time_end)) from @t

--Also we can subtract dates
select Time_end - Time_Start from @t

Armed with this knowledge we can easily calculate averages
We can also use the float datatype wherever a datetime is expected and it will be converted implicitly.

declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '18000101 00:00:00', '20000102 00:00:01', 'Y'

;with dur as
(
select dur = Time_end - Time_Start
from @t
)
select convert(datetime,avg(convert(float,dur.dur)))
from dur

And we can exclude entries
declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '18000101 00:00:00', '20000102 00:00:01', 'N'

;with dur as
(
select dur = Time_end - Time_Start
from @t
where Include = 'Y'
)
select convert(datetime,avg(convert(float,dur.dur))), avg(convert(float,dur.dur))
from dur

-- and to format the result

declare @t table (Time_Start datetime, Time_end datetime, include varchar(1))
insert @t select '20000101 00:00:00', '20000101 23:59:59', 'Y'
insert @t select '20000101 00:00:00', '20000102 00:00:01', 'Y'
insert @t select '20000101 00:00:00', '20000102 10:05:13', 'Y'
insert @t select '18000101 00:00:00', '20000102 10:05:13', 'N'

;with dur as
(
select dur = Time_end - Time_Start
from @t
where Include = 'Y'
)
, dur_avg as
(
select	dur_d = convert(datetime,avg(convert(float,dur.dur)))
, dur_f = avg(convert(float,dur.dur))
from dur
)
select	  dur_d = dur_d
, dur_f = dur_f
, days_f = convert(int,dur_f)
, days_d = datediff(dd,0,dur_d)
, tot_hrs = convert(int,dur_f)*24 + datepart(hh,dur_f)
-- using date functions
, dd_d = datediff(dd,0,dur_d)		-- cannot use datepart as will have a limit of 31 days
, hh_d = datepart(hh,dur_d)
, mm_d = datepart(mi,dur_d)
, ss_d = datepart(ss,dur_d)
-- calculating from float - subtracting the higher parts
, dd_f = convert(int,dur_f)
, hh_f = convert(int,24*(dur_f - convert(int,dur_f)))
, mm_f = convert(int,
60*24*(dur_f - convert(int,dur_f))
- 60*convert(int,24*(dur_f - convert(int,dur_f)))
)
, ss_f = convert(int,
60*60*24*(dur_f - convert(int,dur_f))
- 60*convert(int,24*60*(dur_f - convert(int,dur_f)))
)
-- calculating from float - using modulus to remove higher parts
, dd_f2 = convert(int,dur_f)
, hh_f2 = convert(int,24*(dur_f - convert(int,dur_f)))
, mm_f2 = convert(int,60*24*(dur_f - convert(int,dur_f)))%60
, ss_f2 = convert(int,60*60*24*(dur_f - convert(int,dur_f)))%60
from dur_avg

home