Get Time Duration in [DD hh:mm:ss] format

As DBA, there are various scenarios where I display duration in results. More often, for visual effects, I like to convert the same duration into [DD hh:mm:ss] format.

For example, for representing the duration of total waits, resource waits & signal waits for wait types from [sys].[dm_os_wait_stats].[wait_time_ms] on Grafana dashboard by converting to [DD hh:mm:ss] format:-

Also, for displaying duration of query execution by converting [sys].[dm_exec_request].[start_time] into [DD hh:mm:ss] format :-

For this purpose, I wrote below TSQL function [time2duration] that provides me duration in [DD hh:mm:ss] format:-

use master
go

create function time2duration (@time varchar(27), @unit varchar(20) = 'second')
returns varchar(30)
as
begin
	declare @duration varchar(30);

	if @unit in ('datetime','datetime2','smalldatetime')
	begin
		select @duration =
				Concat
					(
						RIGHT('00'+CAST(ISNULL((datediff(second,@time,GETDATE()) / 3600 / 24), 0) AS VARCHAR(2)),2)
						,' '
						,RIGHT('00'+CAST(ISNULL(datediff(second,@time,GETDATE()) / 3600  % 24, 0) AS VARCHAR(2)),2)
						,':'
						,RIGHT('00'+CAST(ISNULL(datediff(second,@time,GETDATE()) / 60 % 60, 0) AS VARCHAR(2)),2)
						,':'
						,RIGHT('00'+CAST(ISNULL(datediff(second,@time,GETDATE()) % 3600 % 60, 0) AS VARCHAR(2)),2)
					) --as [dd hh:mm:ss]
	end

	if @unit in ('second','ss','s')
	begin
		select @duration =
				Concat
					(
						RIGHT('00'+CAST(ISNULL((@time / 3600 / 24), 0) AS VARCHAR(2)),2)
						,' '
						,RIGHT('00'+CAST(ISNULL(@time / 3600  % 24, 0) AS VARCHAR(2)),2)
						,':'
						,RIGHT('00'+CAST(ISNULL(@time / 60 % 60, 0) AS VARCHAR(2)),2)
						,':'
						,RIGHT('00'+CAST(ISNULL(@time % 3600 % 60, 0) AS VARCHAR(2)),2)
					) --as [dd hh:mm:ss]
	end

	return (@duration);
end
go

Below are 2 sample usage for above function:-

-- Examples
select /* ParameterSet 01 -> Convert 429424 seconds into [DD hh:mm:ss] */
	   [DD hh:mm:ss] = master.dbo.time2duration(429424,'s')
		/* ParameterSet 02 -> Convert duration since '2020-07-24 11:28:10.133' into [DD hh:mm:ss] */
	   ,[DD hh:mm:ss] = master.dbo.time2duration('2020-07-24 11:28:10.133','datetime2');
go

I hope this will be useful to you 🙂

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.