How to Calculate Time Operations in SQL Server on Time Data Type
It is sometimes necessary to deal with time variables, take average of time columns or a group of rows including time fields. Frequently, t-sql developers sum time fields and then return total time variable as and expression in hour, minute and seconds.
To complete such a time operation task, I generally choose to convert time variable into seconds. Then after completing mathematical operations over time values expressed in seconds, I can return back the final result as a time variable. It is easy to convert time expressed in seconds into a time variable with hours, minutes and seconds part in it.
This T-SQL tutorial will be showing how to convert time into seconds and then covert it back to time variable from pure seconds expression.
The following SQL script gets the time part of GetDate() function into a SQL Server time variable @t.
The time value @t is expressed as a time period in seconds. This requires the following calculation.
Multiply hours with 3600 seconds/hour
Multiply minutes with 60 seconds/hour
Add hour-to-seconds, minutes-to-seconds and remaining seconds part into resultant @total integer variable.
select @total = DATEPART(ss,@t) + 60 * DATEPART(mi,@t) + 3600 * DATEPART(hh,@t)
declare @t as time = getdate()
declare @total as bigint
select @total = DATEPART(ss,@t) + 60 * DATEPART(mi,@t) + 3600 * DATEPART(hh,@t)
select
@t [Current Time],
@total [Total Time in Seconds],
(@total / 3600) [Total Time Hour Part],
((@total % 3600) / 60) [Total Time Minute Part],
(@total % 60) [Total Time Second Part]
The second SELECT statement re-calculates the time parts (hour, minute, second) of the @total time in seconds.
This sql calculation is simple.
Divide @total period expressed in seconds into 3600. This returns hours
Take MOD to 3600 of @total time period in seconds. Then divide resultant value into 60 for minutes
Take MOD to 60 of @total time for seconds.
SQL developers can use the above logic to express a time span which is expressed in seconds as a time variable with hour, minute and seconds part.
Here is the output of the above t-sql script.
Convert Time Variable into Seconds
Here is a user-defined SQL function that programmers can use to convert time value into seconds. I'll use this function in the following sql codes within this t-sql tutorial.
Create Function fn_CalculateTimeInSeconds (
@time datetime -- @time time
) Returns Int
AS
BEGIN
RETURN DATEPART(ss,@time) + 60 * DATEPART(mi,@time) + 3600 * DATEPART(hh,@time)
END
Sample Table Data with Time Column
Now let's set up a SQL problem. Assume that you simulate the calculation of marathon running results. Here is a database table where you keep results of the marathon with finish time in SQL time variable. The CREATE TABLE command is followed by sample data generating INSERT statement.
Create Table MarathonResults (
Id int identity(1,1),
Runner varchar(50),
Category varchar(25),
FinishTime time
);
Insert Into MarathonResults Values
('Bill Gates','Male','1:45:40'),
('Diana Parker','Female','2:10:30'),
('Darth Vader','Male','1:35:27'),
('Kit Walker','Male','1:15:12'),
('Padme Amidala','Female','2:15:15')
Now we can calculate average results in each runner category and overall average simply using SQL AVG() aggregate function over finish time converted into seconds using the user-defined function fn_CalculateTimeInSeconds
SELECT
*,
dbo.fn_CalculateTimeInSeconds(FinishTime) TotalInSeconds,
AVG( dbo.fn_CalculateTimeInSeconds(FinishTime) ) OVER (Partition By Category) AverageByCategory,
AVG( dbo.fn_CalculateTimeInSeconds(FinishTime) ) OVER (Partition By 1) Average
FROM MarathonResults
Please note that the SQL AVG() function enhancement with the OVER clause. The Partition By clause is grouping the results into categories. And a dummy "Partition By 1" clause returns an overall Average calculation over all result set.
Convert Time in Seconds into Time Variable
Now SQL developers need a function which will convert the integer seconds value into a time data type value.
Check the following user function fn_CreateTimeFromSeconds SQL code.
You will realize that the division and mod operations are familiar from the tutorial entry section.
The only different part is string formatting with '00' concatenation and Right() string function.
This sql codes are necessary to format the results in HH:MI:SS format.
Create Function fn_CreateTimeFromSeconds (
@seconds int
) Returns time
BEGIN
Return
CAST (
RIGHT('00' + CAST( (@seconds / 3600) as varchar(2)), 2) + ':' +
RIGHT('00' + CAST( ((@seconds % 3600) / 60) as varchar(2)), 2) + ':' +
RIGHT('00' + CAST( (@seconds % 60) as varchar(2)), 2)
as Time)
END
Time Calculation in SQL Server
Here is the T-SQL CTE expression which makes a time calculation on sample data and returns the results again in SQL Server time data type.
Please note that the below SQL CTE expression runs the three steps required for a SQL time calculation in order:
Convert time into seconds,
Make aritmetic operations using SQL functions,
Convert result into SQL Server time data type.
CTE 1 uses user function fn_CalculateTimeInSeconds() to convert time column FinishTime into seconds with data type integer.
CTE 2 performs SQL arithmetic calculations like AVG() average aggregation function
Last SELECT returns SQL calculation results in SQL Server Time data type using function fn_CreateTimeFromSeconds()
;With CTE1 as (
SELECT *, dbo.fn_CalculateTimeInSeconds(FinishTime) TotalInSeconds
FROM MarathonResults
),
CTE2 as (
Select
CTE1.*,
AVG(TotalInSeconds) OVER (Partition By Category) CategoryAverage,
AVG(TotalInSeconds) OVER (Partition By 1) Average
From CTE1
)
select
Id,
Runner,
Category,
[Rank] = RANK() OVER (Partition By Category Order By TotalInSeconds DESC),
FinishTime,
dbo.fn_CreateTimeFromSeconds(CategoryAverage) CategoryAverage,
dbo.fn_CreateTimeFromSeconds(Average) Average
from CTE2
