Calculating Elapsed Times (SQL Spackle)
-
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 -
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 😎 -
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] -
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
-
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)
-
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 -
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 -
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 -
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 -
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)
-
Gush, I miss Dwain
😎
-
This was removed by the editor as SPAM
- Eirikur Eiriksson wrote:
Gush, I miss Dwain 😎
Ditto that. In memory...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply