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
-- Date modified: 3/12/2009
-- Description: Generates a human readable difference between two dates, in the form '1y 5d 3h 2m 6s 10ms'
-- =============================================
DROP FUNCTION [dbo].[dateDiffHumanReadable]
GO
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
SET @years = 0
SET @days = 0
SET @hours = 0
SET @minutes = 0
SET @seconds = 0
SET @milliseconds = 0
-- @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)
IF (@days < 0) BEGIN
IF (@years > 0) BEGIN
SET @years = @years -1
SET @dateScratch = DATEADD(yy, -1, @dateScratch)
SET @days = DATEDIFF(d, @dateScratch, @dateEnd)
END
END
SET @dateScratch = DATEADD(d, @days, @dateScratch)
-- milliseconds
SET @milliseconds = DATEDIFF(ms, @dateScratch, @dateEnd)
SET @dateScratch = DATEADD(ms, @milliseconds, @dateScratch)
IF (@milliseconds > 999) BEGIN
SET @seconds = @milliseconds / 1000
SET @milliseconds = @milliseconds - (@seconds * 1000)
-- seconds
IF (@seconds > 59) BEGIN
SET @minutes = @seconds / 60
SET @seconds = @seconds - (@minutes * 60)
-- minutes
IF @minutes > 59 BEGIN
SET @hours = @minutes / 60
SET @minutes = @minutes - (@hours * 60)
END
END
END
-- 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
UPDATE:
Adam correctly pointed out that my function didn’t work when the time span was less than 1 day because:
SELECT DATEDIFF(dd, ‘11:50:48.000′, ‘12:12:45.000′)
Will return 1 rather than 0.
I have updated the function to use DATEDIFF to calculate the year, then use a ‘ms’ calculation to count up for the other values.
