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:-
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 |
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:-
|
1 2 3 4 5 6 |
-- 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 🙂
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
How about this approach
DECLARE @date1 DATETIME, @date2 DATETIME, @date3 DATETIME
SET @date1 = ’09 August 2022 09:00:00′
SET @date2 = ’10 December 2022 23:00:01′
SELECT @date3 = @date2 – @date1
SELECT CONVERT (VARCHAR(3), DATEDIFF(DAY,’1900-01-01′,@date3)) + ‘ days, ‘ + CONVERT(VARCHAR(8),@date3,114)
Looks like clean code to me. Thanks for sharing.
For [sys].[dm_exec_requests] i use the follwing sql on the millisecond columns
DECLARE @milliseconds_elapsed int
SET @milliseconds_elapsed = 174520549
SELECT CONVERT(VARCHAR(3),DATEDIFF(DAY,’1900-01-01′,DATEADD(MILLISECOND ,@milliseconds_elapsed,’1900-01-01′) – ‘1900-01-01’)) + ‘d, [‘ + CONVERT(VARCHAR(8),DATEADD(MILLISECOND ,@milliseconds_elapsed,’1900-01-01’) – ‘1900-01-01’,114) + ‘]’ ElapsedTime
thank you dear Ajay Dwivedi