Calculating Elapsed Times (SQL Spackle)

  • Dwain Camps

    SSC Guru

    Points: 86893

    Comments posted to this topic are about the item Calculating Elapsed Times (SQL Spackle)


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • lkent

    SSC Enthusiast

    Points: 119

    In the healthcare industry, length of stay (LOS) is always fun figuring out how to present the data.

    Usually we only count days stay with a default of 1. But there have been times I've wanted to display the actual length of stay with:

    ## days ## hours ## minutes ## seconds

    I dream of a time when there is a function like: http://www.elapsedtimecalculator.com/Elapsed-Time-Calculator.aspx

    "I like spaghetti because my house is made of brick."
    ~Paul Wuerzner on illogicality 2/14/86 - 11/6/11 😎

  • Mauve

    SSChampion

    Points: 11316

    lkent (9/26/2013)


    In the healthcare industry, length of stay (LOS) is always fun figuring out how to present the data.

    Usually we only count days stay with a default of 1. But there have been times I've wanted to display the actual length of stay with:

    ## days ## hours ## minutes ## seconds

    I dream of a time when there is a function like: http://www.elapsedtimecalculator.com/Elapsed-Time-Calculator.aspx

    You mean like the nice Oracle "interval" data type (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) with all of the nice built-in functions that come with it. Been around since Oracle 9.x (well over 10 years ago).:-)

    SQL Server may eventually support it -- maybe in the next century. 😀


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • David Rueter

    SSCrazy

    Points: 2642

    To display elapsed time as Days Hours:Minutes:Seconds I use a secondsToChar() function I wrote, like this:

    PRINT dbo.secondsToChar(DATEDIFF(second, '2/21/2013 4:43am', '4/2/2013 3:21pm'))

    The result is:

    [highlight="#E0E0E0"]40d 10:38:00[/highlight]

    The function is defined as:

    CREATE FUNCTION [dbo].[secondsToChar] (@ThisSec bigint)

    RETURNS varchar(100)

    --by David Rueter (drueter@assyst.com)

    AS

    BEGIN

    DECLARE @Return varchar(100)

    DECLARE @day int

    DECLARE @hr int

    DECLARE @min-2 int

    DECLARE @sec int

    DECLARE @IsNegative bit

    IF @ThisSec < 0 SET @IsNegative = 1

    SET @ThisSec = ABS(@ThisSec)

    SET @day = ROUND(@ThisSec / 86400, 0)

    SET @ThisSec = @ThisSec - (86400 * @day)

    SET @hr = ROUND(@ThisSec / 3600, 0)

    SET @ThisSec = @ThisSec - (3600 * @hr)

    SET @min-2 = ROUND(@ThisSec / 60, 0)

    SET @ThisSec = @ThisSec - (60 * @min-2)

    SET @sec = @ThisSec

    SET @Return = CAST(@hr AS varchar(10))

    IF @hr < 10 SET @Return = RIGHT('00' + CAST(@hr AS varchar(10)), 2)

    SET @Return = @Return +

    ':' + RIGHT('00' + CAST(@min AS varchar(10)), 2) +

    ':' + RIGHT('00' + CAST(@sec AS varchar(10)), 2)

    IF @day > 0 SET @Return = CAST(@day AS varchar(10)) + 'd ' + @Return

    IF @IsNegative = 1 SET @Return = '-' + @Return

    RETURN @Return

    END

  • tbruce-3350

    Say Hey Kid

    Points: 662

    lkent (9/26/2013)


    In the healthcare industry, length of stay (LOS) is always fun figuring out how to present the data.

    Usually we only count days stay with a default of 1. But there have been times I've wanted to display the actual length of stay with:

    ## days ## hours ## minutes ## seconds

    I dream of a time when there is a function like: http://www.elapsedtimecalculator.com/Elapsed-Time-Calculator.aspx

    For what it's worth I'm sure there are a plethora of methods but here's one that mimics the Elapsed Time Calculator and could be converted into a function:

    DECLARE @StartTime datetime, @EndTime datetime

    SELECT @StartTime = '09/23/2013 8:05:57 AM',

    @EndTime = '09/26/2013 6:03:34 PM'

    SELECT [ElapsedTimeCalculator] =

    CASE WHEN D.Days = 0 THEN '' ELSE CONVERT(varchar,D.Days) + ' Days, ' END

    + CONVERT(varchar,H.Hours) + ' Hours, '

    + CONVERT(varchar,M.Minutes) + ' Minutes, '

    + CONVERT(varchar,M.Seconds) + ' Seconds'

    FROM ( VALUES (DATEDIFF(S, @StartTime, @EndTime)) ) AS S(Seconds)

    CROSS APPLY ( VALUES (S.Seconds / 86400, S.Seconds % 86400) ) AS D(Days, Seconds)

    CROSS APPLY ( VALUES (D.Seconds / 3600, D.Seconds % 3600) ) AS H(Hours, Seconds)

    CROSS APPLY ( VALUES (H.Seconds / 60, H.Seconds % 60) ) AS M(Minutes, Seconds)

  • Dwain Camps

    SSC Guru

    Points: 86893

    Mauve (9/26/2013)


    lkent (9/26/2013)


    In the healthcare industry, length of stay (LOS) is always fun figuring out how to present the data.

    Usually we only count days stay with a default of 1. But there have been times I've wanted to display the actual length of stay with:

    ## days ## hours ## minutes ## seconds

    I dream of a time when there is a function like: http://www.elapsedtimecalculator.com/Elapsed-Time-Calculator.aspx

    You mean like the nice Oracle "interval" data type (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) with all of the nice built-in functions that come with it. Been around since Oracle 9.x (well over 10 years ago).:-)

    SQL Server may eventually support it -- maybe in the next century. 😀

    I have groused before about SQL not having an interval data type. I tried to tone it down in this article. 😀


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • Dwain Camps

    SSC Guru

    Points: 86893

    tbruce-3350 (9/26/2013)


    lkent (9/26/2013)


    In the healthcare industry, length of stay (LOS) is always fun figuring out how to present the data.

    Usually we only count days stay with a default of 1. But there have been times I've wanted to display the actual length of stay with:

    ## days ## hours ## minutes ## seconds

    I dream of a time when there is a function like: http://www.elapsedtimecalculator.com/Elapsed-Time-Calculator.aspx

    For what it's worth I'm sure there are a plethora of methods but here's one that mimics the Elapsed Time Calculator and could be converted into a function:

    DECLARE @StartTime datetime, @EndTime datetime

    SELECT @StartTime = '09/23/2013 8:05:57 AM',

    @EndTime = '09/26/2013 6:03:34 PM'

    SELECT [ElapsedTimeCalculator] =

    CASE WHEN D.Days = 0 THEN '' ELSE CONVERT(varchar,D.Days) + ' Days, ' END

    + CONVERT(varchar,H.Hours) + ' Hours, '

    + CONVERT(varchar,M.Minutes) + ' Minutes, '

    + CONVERT(varchar,M.Seconds) + ' Seconds'

    FROM ( VALUES (DATEDIFF(S, @StartTime, @EndTime)) ) AS S(Seconds)

    CROSS APPLY ( VALUES (S.Seconds / 86400, S.Seconds % 86400) ) AS D(Days, Seconds)

    CROSS APPLY ( VALUES (D.Seconds / 3600, D.Seconds % 3600) ) AS H(Hours, Seconds)

    CROSS APPLY ( VALUES (H.Seconds / 60, H.Seconds % 60) ) AS M(Minutes, Seconds)

    Looks like a pretty efficient technique. Thanks for the added value.

    But you do need to be careful about one thing though:

    DECLARE @StartTime datetime, @EndTime datetime

    SELECT @StartTime = '09/23/1900 8:05:57 AM',

    @EndTime = '09/26/2013 6:03:34 PM'

    SELECT [ElapsedTimeCalculator] =

    CASE WHEN D.Days = 0 THEN '' ELSE CONVERT(varchar,D.Days) + ' Days, ' END

    + CONVERT(varchar,H.Hours) + ' Hours, '

    + CONVERT(varchar,M.Minutes) + ' Minutes, '

    + CONVERT(varchar,M.Seconds) + ' Seconds'

    FROM ( VALUES (DATEDIFF(S, @StartTime, @EndTime)) ) AS S(Seconds)

    CROSS APPLY ( VALUES (S.Seconds / 86400, S.Seconds % 86400) ) AS D(Days, Seconds)

    CROSS APPLY ( VALUES (D.Seconds / 3600, D.Seconds % 3600) ) AS H(Hours, Seconds)

    CROSS APPLY ( VALUES (H.Seconds / 60, H.Seconds % 60) ) AS M(Minutes, Seconds)

    Msg 535, Level 16, State 0, Line 5

    The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • SumOfDavid

    Ten Centuries

    Points: 1357

    I solved this issue by using the SQL# (www.sqlsharp.com) library. The Date_FormatTimeSpan function (which is part of the free functionality) is very flexible. For example, here are 3 different ways you can use this function to format a time span: Read the documentation for an explanation of the format code.

    DECLARE @dt date = CAST(DATEADD(dd,-2,GETDATE()) AS date), -- minus two days and the time component

    -- format to print the time values and then the intervals

    @s1 nvarchar(1000) = N'%{dd;;%d;;%d day;;%d days} %{hh;;%d;;%d hour;;%d hours} %{mi;;%d;;%d minute;;%d minutes} %{ss;;0 seconds;;%d second;;%d seconds}',

    -- format to print the time intevals first and then the values

    @s2 nvarchar(1000) = N'%{dd;;Days: 00;;Days: %2d;;Days: %2d} %{hh;;Hours: 00;;Hours: %2d;;Hours: %2d} %{mi;;Minutes: 00;;Minutes: %2d;;Minutes: %2d} %{ss;;Seconds: 00;;Seconds: %2d;;Seconds: %2d}',

    -- format to print the time values in YY:MM:DD:HH:MM:SS format

    @s3 nvarchar(1000) = N'%{yy;;%2d;;%2d;;%2d}:%{mm;;%2d;;%2d;;%2d}:%{dd;;%2d;;%2d;;%2d}:%{hh;;%2d;;%2d;;%2d}:%{mi;;%2d;;%2d;;%2d}:%{ss;;%2d;;%2d;;%2d}';

    PRINT 'Begin Time = ' + CONVERT(varchar(100),@dt,120);

    PRINT ' End Time = ' + CONVERT(varchar(100),GETDATE(),120);

    PRINT ''

    PRINT ' --- Elapsed Time Format #1 ---'

    PRINT SQL#.Date_FormatTimeSpan(@dt, GETDATE(), @s1);

    PRINT ''

    PRINT ' --- Elapsed Time Format #2 ---'

    PRINT SQL#.Date_FormatTimeSpan(@dt, GETDATE(), @s2);

    PRINT ''

    PRINT ' --- Elapsed Time Format #3 ---'

    PRINT SQL#.Date_FormatTimeSpan(@dt, GETDATE(), @s3);

    You can create your own wrapper function with some simple constants for the different format types you might use in your application. (can't imagine you'd have too many different formats in the same application)

    The results look like:

    [font="Courier New"]

    Begin Time = 2013-09-24

    End Time = 2013-09-26 18:04:15

    --- Elapsed Time Format #1 ---

    2 days 18 hours 4 minutes 15 seconds

    --- Elapsed Time Format #2 ---

    Days: 02 Hours: 18 Minutes: 04 Seconds: 15

    --- Elapsed Time Format #3 ---

    00:00:02:18:04:15

    [/font]


    David

  • Dwain Camps

    SSC Guru

    Points: 86893

    I like the SQL# library too.

    Note that this article was based on a "call for an article" by this site's editor, which constrained how the input data was to appear. So I sort of followed that, without saying that there may be other (perhaps better) ways to store the input data. And of course to handle elapsed times that span dates.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • tbruce-3350

    Say Hey Kid

    Points: 662

    Looks like a pretty efficient technique. Thanks for the added value.

    But you do need to be careful about one thing though:

    Msg 535, Level 16, State 0, Line 5

    The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

    Thanks for the feedback Dwain. This "solution" was a little off the topic of your original post so my apologies for the conversation creep. I've been reading your recent articles regarding gaps and islands and how some of that logic can be utilized for other purposes such as your spackle post here. We have some cases at work where these ideas should prove useful over current methods so thank you for providing and helping spur new ways of approach.

    As for my original post I freely admit it hadn't been thoroughly tested for longer date ranges. It was a first crack and mainly I didn't anticipate (nor wish for) anyone being in the hospital for over 68 years. Assumptions were made, data was hurt... That said, mainly for my own sake here's a slightly adjusted version which should allow inputs for any dates between 1753 and 9999 for either the start or end dates. Granted it's a specific solution for a specific situation but it was a mildly intriguing thought exercise for the end of the week.

    DECLARE @StartTime datetime, @EndTime datetime

    SELECT @StartTime = '01/01/1753 00:00:00 AM',

    @EndTime = '12/31/9999 11:59:59 PM'

    SELECT [ElapsedTimeCalculator] =

    CASE WHEN D.Days = 0 THEN '' ELSE CONVERT(varchar,D.Days) + ' Days, ' END

    + CONVERT(varchar,H.Hours) + ' Hours, '

    + CONVERT(varchar,M.Minutes) + ' Minutes, '

    + CONVERT(varchar,M.Seconds) + ' Seconds'

    FROM ( VALUES ( DATEDIFF(D, @StartTime, @EndTime), DATEADD(D, DATEDIFF(D, @StartTime, @EndTime), @StartTime) ) ) AS Adj(InitialDays, AdjStartTime)

    CROSS APPLY ( VALUES (Adj.InitialDays + CASE

    WHEN Adj.AdjStartTime > @EndTime AND @StartTime < @EndTime THEN -1

    WHEN Adj.AdjStartTime < @EndTime AND @StartTime > @EndTime THEN 1 ELSE 0 END) ) AS D(Days)

    CROSS APPLY ( VALUES (DATEDIFF(S, DATEADD(D, D.Days, @StartTime), @EndTime)) ) AS T(TimeRemaining)

    CROSS APPLY ( VALUES (T.TimeRemaining / 3600, T.TimeRemaining % 3600) ) AS H(Hours, TimeRemaining)

    CROSS APPLY ( VALUES (H.TimeRemaining / 60, H.TimeRemaining % 60) ) AS M(Minutes, Seconds)

  • Eirikur Eiriksson

    SSC Guru

    Points: 182440

    Gush, I miss Dwain

    😎

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply