SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



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.

TSQL convert time into seconds and time parts


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.

SQL Average calculation on time variables


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

time calculation in SQL Server





Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers










Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems