All the time, when I’m writing reports I need to display a ‘time elapsed’ metric in a short easy to read manor. Usually I do this with a simple convenience function in whatever language I am writing the GUI in. For reports that are purely in SQL, I have created just such a convenience function for SQL server: dateDiffHumanReadable( startDate, endDate, precision ).
This makes it easy to display date spans.
To see how old I currently am, I would call:
DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), DEFAULT)
This will return my current age:
30y 114d 38m 11s 620ms
This comes in handy when I have a log table with columns like this:
- process
- timeStart
- timeEnd
Just using DATEDIFF I would have to do this:
SELECT process,
CASE WHEN timeStart IS NOT NULL AND timeEnd IS NOT NULL THEN
DATEDIFF(s, timeStart, timeEnd)
ELSE
''
END AS [seconds]
FROM logTable
With this function, I can get more informational results just doing this:
SELECT process, dbo.dateDiffHumanReadable(timeStart, timeEnd, DEFAULT)
FROM logTable
The third parameter is a precision value for when you don’t need right down to the millisecond returned. Calling this:
DECLARE @birthday DATETIME
SET @birthday = '6/2/1978 16:00:00'
PRINT dbo.dateDiffHumanReadable(@birthday, GETDATE(), 'y')
This will return just the year of my current age:
30y
Full code
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: T. Curran
-- Create date: 3/20/2008
-- Description: Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms'
-- =============================================
CREATE FUNCTION [dbo].[dateDiffHumanReadable]
(
@dateStart DATETIME,
@dateEnd DATETIME,
@PRECISION VARCHAR(2) = 'ms'
)
RETURNS VARCHAR(200)
AS
BEGIN
DECLARE @diffAsString VARCHAR(200),
@dateScratch DATETIME,
@years INT,
@days INT,
@hours INT,
@minutes INT,
@seconds INT,
@milliseconds INT
SET @diffAsString = ''
IF (@dateStart IS NULL OR @dateEnd IS NULL) BEGIN
RETURN @diffAsString
END
-- @dateScratch is used as a holding place for us to increment the date so we don't alter @dateStart
SET @dateScratch = @dateStart
-- years
SET @years = DATEDIFF(yy, @dateScratch, @dateEnd)
SET @dateScratch = DATEADD(yy, @years, @dateScratch)
-- days
SET @days = DATEDIFF(d, @dateScratch, @dateEnd)
SET @dateScratch = DATEADD(d, @days, @dateScratch)
-- hours
SET @hours = DATEDIFF(hh, @dateScratch, @dateEnd)
SET @dateScratch = DATEADD(hh, @hours, @dateScratch)
-- minutes
SET @minutes = DATEDIFF(mi, @dateScratch, @dateEnd)
SET @dateScratch = DATEADD(mi, @minutes, @dateScratch)
-- seconds
SET @seconds = DATEDIFF(s, @dateScratch, @dateEnd)
SET @dateScratch = DATEADD(s, @seconds, @dateScratch)
-- milliseconds
SET @milliseconds = DATEDIFF(ms, @dateScratch, @dateEnd)
SET @dateScratch = DATEADD(ms, @milliseconds, @dateScratch)
-- Build the output string based on the precision
-- years
IF (@PRECISION = 'y' OR @PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
IF (@years > 0) BEGIN
SET @diffAsString = @diffAsString + CAST(@years AS VARCHAR(10)) + 'y '
END
END
-- days
IF (@PRECISION = 'd' OR @PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
IF (@days > 0) BEGIN
SET @diffAsString = @diffAsString + CAST(@days AS VARCHAR(10)) + 'd '
END
END
-- hours
IF (@PRECISION = 'h' OR @PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
IF (@hours > 0) BEGIN
SET @diffAsString = @diffAsString + CAST(@hours AS VARCHAR(10)) + 'h '
END
END
-- minutes
IF (@PRECISION = 'm' OR @PRECISION = 's' OR @PRECISION = 'ms') BEGIN
IF (@minutes > 0) BEGIN
SET @diffAsString = @diffAsString + CAST(@minutes AS VARCHAR(10)) + 'm '
END
END
-- seconds
IF (@PRECISION = 's' OR @PRECISION = 'ms') BEGIN
IF (@seconds > 0) BEGIN
SET @diffAsString = @diffAsString + CAST(@seconds AS VARCHAR(10)) + 's '
END
END
-- milliseconds
IF (@PRECISION = 'ms') BEGIN
IF (@milliseconds > 0) BEGIN
SET @diffAsString = @diffAsString + CAST(@milliseconds AS VARCHAR(10)) + 'ms '
END
END
-- the above string concat always ends with a space, if the space is there at the end remove it
IF (LEN(@diffAsString) > 0) BEGIN
IF (RIGHT(@diffAsString, 1) = '') BEGIN
SET @diffAsString = LEFT(@diffAsString, LEN(@diffAsString))
END
END
RETURN @diffAsString
END
GO
