Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Calculating Duration Using DATETIME Start and End Dates (SQL Spackle) Expand / Collapse
Author
Message
Posted Thursday, January 16, 2014 8:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
Peter E. Kierstead (1/16/2014)

Try this...



Create Function dbo.ElapsedTime
(
@StartTime DateTime,
@EndTime DateTime
)
Returns VarChar(20)
As
Begin
Declare
@st DateTime,
@et DateTime,
@NeedOffset bit;

-- Reality check!
If (@StartTime>@EndTime) Return Null;

Select
-- Get isolated time components for start and end DateTime inputs.
@st='1900-01-01 '+Right(Convert(VarChar,@StartTime,121),12),
@et='1900-01-01 '+Right(Convert(VarChar,@EndTime,121),12),
-- If there is a time inversion set a flag indicating that offsetting is required.
@NeedOffset=Case when @st>@et then 1 else 0 End;

-- The DateTime data-type can represent dates between 1753-01-01 00:00:00.000 and
-- 9999-12-31 23:59:59.998, inclusive. However, DateDiff(ms,...) will throw an Int
-- overflow exception if the differential is close to 25 days, therefore, this method
-- processes the days component separately from the time component.
Return
-- Output the DateTime days differential (0 - 3012153).
Cast
(
DateDiff(dd,@StartTime,@EndTime)-
-- Apply day offset, if necessary.
Case @NeedOffset when 1 then 1 else 0 End
as VarChar
)+
':'+
-- Output the DateTime time differential (00:00:00.000 - 23:59:59.999).
-- This is done by replacing both input value's date component with
-- '1900-01-01' before running the appropriate conversion functions.
Right
(
Convert
(
VarChar,
DateAdd
(
ms,
DateDiff
(
ms,
@st,
@et
)+
-- Handle milliseconds offset, if necessary.
Case @NeedOffset when 1 then 86400000 else 0 End,
0
),
121
),
12
);
End



As I said, I'm always up for alternate methods and very much appreciate your time in posting that code. I did try it on the million row test table and it took over 26 seconds to complete where the same code (both executed on my slower 12 year old box) only took a little over 3 . Part of the problem is that it's a scalar function, which will usually mean that, all else being equal, it will run about 7 times slower than direct code or an iSF (proverbial "Inline Scalar Function", which is really "just" an Inline Table Valued Function that returns a scalar value).

While that doesn't seem to be a big difference to many, things like this really start to add up in the face of scalability.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1531905
Posted Thursday, January 16, 2014 8:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
dwain.c (1/16/2014)
I must say, I love the technique Jeff! Very simple, straightforward and clean with good performance. Nothing more straightforward comes immediately to mind (issues noted above aside).

Normally I take your word on performance but I in this case I played a bit and found something interesting. Running your 1M row test harness, followed by the below query:

    SET STATISTICS TIME ON;
SELECT @BitBucket = STUFF(CONVERT(VARCHAR(20),s,114),1,2,DATEDIFF(hh,0,s))
FROM #JBMTest
CROSS APPLY (SELECT EndDT-StartDT) a (s);
SET STATISTICS TIME OFF;


Generated different speed results on SQL 2008 R2 vs. SQL 2012.

SQL 2008 R2 (expected - second result uses CA for intermediate calculation):

(1000000 row(s) affected)

SQL Server Execution Times:
CPU time = 702 ms, elapsed time = 699 ms.

SQL Server Execution Times:
CPU time = 717 ms, elapsed time = 707 ms.


SQL 2012 (unexpected):

(1000000 row(s) affected)

SQL Server Execution Times:
CPU time = 749 ms, elapsed time = 738 ms.

SQL Server Execution Times:
CPU time = 671 ms, elapsed time = 674 ms.


Improvements in the optimizer I reckon.


Thanks for the feedback, Dwain. Like I said in the article, most of the time was spent on display and the actual time expended for the calculation was right around 717ms using the "@BitBucket" method for taking the display out of the picture. Your code certainly backs that claim up.

As for the 2012 code, I'm actually disappointed to see those results and wouldn't call it an "improvement" to the optimizer at all. It seems to indicate that you need more complex code to get the same performance out of what used to be simpler code.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1531908
Posted Thursday, January 16, 2014 8:57 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 3,627, Visits: 5,269
Jeff Moden (1/16/2014)
[quote]dwain.c (1/16/2014)

As for the 2012 code, I'm actually disappointed to see those results and wouldn't call it an "improvement" to the optimizer at all. It seems to indicate that you need more complex code to get the same performance out of what used to be simpler code.


I can see that perspective. It does open interesting possibilities though.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1531910
Posted Thursday, January 16, 2014 9:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
rho_pooka (1/16/2014)
Thank you for article, enjoyed the read!


Thank you very much for the feedback. I appreciate it.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1531911
Posted Thursday, January 16, 2014 9:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
Thomas Abraham (1/16/2014)
I meant to post this earlier, but got caught up in some of the tangent. Thanks to Jeff for providing this spackle. Great job, as always.


Thanks for the feedback and for the cover in my absence. Great job, Thomasc and thank you very much for the help.


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1531912
Posted Thursday, January 16, 2014 9:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
Since there was some interest in correctly calculating the duration even if the start and end dates were reversed, here's a function that straightens that out. Personally, I'd much rather ensure that the data in the table were actually correct or that an application was passing the dates correctly but this "iSF" function would do just fine (it's only a little slower than the original code) if such things were not possible.

 CREATE FUNCTION dbo.Duration
/**********************************************************************************************************************
Purpose:
Given a start and end date as a DATETIME, regardless of the temporal order, calculate the duration between the lowest
datetime and the highest and format the output as HHH:MI:SS.nnn WHERE "HHH" is any number of hours up to the maximum
maximum difference that DATEDIFF and CONVERT can handle (MAX date of 9999-01-01 23:59:59.000 or 71,003,135 hours or
3ms less than 10,000 years).

Obviously, if either value is NULL, a NULL will be returned.

Usage Example:
SELECT ca.FormattedDuration
FROM #JBMTest tt
CROSS APPLY dbo.Duration(tt.StartDT, tt.EndDT) ca

Ref: http://www.sqlservercentral.com/articles/T-SQL/103343/

Revision History:
Rev 00 - 16 Jan 2014 - Jeff Moden - Initial creation and unit test.
**********************************************************************************************************************/
(
@StartDT DATETIME
,@EndDt DATETIME
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH cteSortDates AS
(--==== Sort the 2 dates with a "short circuit" in the CASEs for the expected order.
SELECT StartDT = CASE WHEN @StartDT <= @EndDT THEN @StartDT ELSE @EndDT END
,EndDT = CASE WHEN @StartDT <= @EndDT THEN @EndDT ELSE @StartDT END
)
SELECT FormattedDuration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))
FROM cteSortDates
;




--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1531916
Posted Thursday, January 16, 2014 10:07 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:35 AM
Points: 36,977, Visits: 31,494
Crud. I just noticed a limitation and I need to post another correction to the article. I tested for all dates from 1900-01-01 00:00:00.000 through 9999-12-31 23:59:59.997. If you have just 3ms more time span, the code fails with an overflow. This is apparently because CONVERT has a limit the same as the MAX SQL DateTime of 9999-12-31-23:59:59.997 (and why wouldn't it?). Even just 3ms more, and you get an overflow error. That means this method has a limit of 3ms less than 10,000 years. Still, that's better than 148 years but it's still an error in the article that needs to be corrected, which I'll do in the morning.

Here's the code that demonstrates the problem above...

--==== This works.
SELECT CONVERT(VARCHAR(20),CAST('9999-12-31 23:59:59.997' AS DATETIME)-CAST('1900-01-01 00:00:00.000' AS DATETIME),114)
;
--==== Adding just 3ms more duration causes CONVERT to fail.
SELECT CONVERT(VARCHAR(20),CAST('9999-12-31 23:59:59.997' AS DATETIME)-CAST('1899-12-31 23:59:59.997' AS DATETIME),114)
;



--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1531918
Posted Thursday, January 16, 2014 10:43 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:22 PM
Points: 190, Visits: 436
Thanks Jeff for the performance analysis. The two main reasons that it is slower are:
1) Its coded as a scalar function
2) It also handles days, i.e., it outputs a string like DDD:HH:MM:SS.mmm

I've altered things a bit and here are the results from 1,000,000 test rows:

Your code outputting [HHH:MM:SS.mmm]:

STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))


CPU time = 1202 ms, elapsed time = 1182 ms.

My refactored code outputting [DDD:HH:MM:SS.mmm]:

-- Emit the days number...
Cast(DateDiff(dd,StartDT,EndDT)
-- ...subtracting a day when the times are inverted.
-Case when Convert(VarChar,StartDT,114)>Convert(VarChar,EndDT,114) then 1 else 0 End as VarChar)
-- Emit the separator and time components.
+':'+Convert(VarChar,EndDT-StartDT,114)


CPU time = 2527 ms, elapsed time = 2381 ms.

I'm willing to eat the extra time to get the elapsed time in DDD:HH:MM:SS.mmm format, especially since we only use this to output elapsed time between DML in procedures during development.

I didn't realize that you could perform simple math (+, -) on DateTime types! Learn something new everyday. Thanks, you rock!




PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1531927
Posted Friday, January 17, 2014 3:10 PM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:19 AM
Points: 7, Visits: 214
Fantastically simple!

As for how to get the duration in YMD, here's what I came up with. I tried to code it the way I would process it in my head (scary, I know!). My approach is that we humans would intuitively account for the differences in the days of the month and leap years by subtracting the dates in part.
1) first the whole years difference
2) then the number of whole months left over from the whole years difference
3) then the number of days left over from the whole months difference. Thus, the difference in days is dependent on the number of days in the month from the start date.

Anyway, for what it's worth here's the SQL I came up with.

DECLARE @StartTime datetime, @EndTime datetime;
SET @StartTime = '1964-07-24';
SET @EndTime = '2014-01-17';

SELECT
CONVERT(varchar(4),
CASE WHEN DATEPART(MONTH,@EndTime) < DATEPART(MONTH,@StartTime)
-- If month of @EndTime is < the month of @StartTime, then there is a fraction of a year between the two dates, so subtract 1 from the year difference
THEN DATEPART(YEAR,@EndTime) - DATEPART(YEAR,@StartTime) - 1
-- If the month of @EndTime is >= the month of @StartTime, then difference the years and take one away if there is only a fraction of a month between the dates (i.e. the day of @EndTime < the day of @StartTime)
ELSE DATEPART(YEAR,@EndTime) - DATEPART(YEAR,@StartTime) - CASE WHEN DATEPART(DAY,@EndTime) < DATEPART(DAY,@StartTime) THEN 1 ELSE 0 END
END
) + 'Y ' +

CONVERT(varchar(4),
CASE WHEN DATEPART(MONTH,@EndTime) <= DATEPART(MONTH,@StartTime)
-- If the month of @EndTime <= the month of @StartTime, then get the difference in months by adding 12 (i.e. one year) to @EndTime
THEN DATEPART(MONTH,@EndTime) + 12 - DATEPART(MONTH,@StartTime)
-- Otherwise, simply subtract the month of @StartTime from the month of @EndTime
ELSE DATEPART(MONTH,@EndTime) - DATEPART(MONTH,@StartTime)
END
-- If month of @EndTime is < month of @StartTime, then there is a fraction of a year between the two dates, so subtract 1 from the month difference
- CASE WHEN DATEPART(DAY,@EndTime) < DATEPART(DAY,@StartTime) THEN 1 ELSE 0 END
-- If the months are the same and the day of @EndTime is >= the day of @StartTime, then subtract 12 to prevent 12M from occurring in the output
-- The logic above for the year difference accounts for the full year
- CASE WHEN DATEPART(MONTH,@EndTime) = DATEPART(MONTH,@StartTime) AND DATEPART(DAY,@EndTime) >= DATEPART(DAY,@StartTime) THEN 12 ELSE 0 END
) + 'M ' +

CONVERT(varchar(4),
CASE WHEN DATEPART(DAY,@EndTime) < DATEPART(DAY,@StartTime)
-- If the day of @EndTime is < the day of @StartTime, then subtract the difference between @StartTime - @EndTime from the number of days in the month of @StartTime
-- This give the intuitive difference in days based on the @StartTime after getting the difference in years and months above.
THEN DATEDIFF(DAY, DATEADD(DAY, 1 - DAY(@StartTime), @StartTime), DATEADD(MONTH, 1, DATEADD(DAY, 1 - DAY(@StartTime), @StartTime))) -- Number of days in the month of @StartTime
- (DATEPART(DAY,@StartTime) - DATEPART(DAY,@EndTime))
-- Otherwise, simply subtract the day of @StartTime from the day of @EndTime
ELSE DATEPART(DAY,@EndTime) - DATEPART(DAY,@StartTime)
END
) + 'D '

AS 'Time Period'



-- RTW
Be curious!
Post #1532306
Posted Friday, January 17, 2014 4:43 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:22 PM
Points: 190, Visits: 436
Another bit of SQL weirdness with the DateTime datatype.

Setup...


According to BOL a DateTime variable will hold dates ranging from 1753-01-01 through 9999-12-31.
By not also specifying the time component they judiciously avoided a few nasty questions. Here are some examples that illuminate this issue.

This example uses implicit casting to convert a date/time string into a DateTime data type; using actual DateTime values will not change the outcome:

Declare
@MinDT DateTime,
@MaxDT DateTime;

Select
@MinDT ='1753-01-01 00:00:00.000',
@MaxDT ='9999-12-31 23:59:59.998'

Select
@MinDT,
@MaxDT;

The final SELECT statement will output:

1753-01-01 00:00:00.000 | 9999-12-31 23:59:59.997

Notice the millisecond difference between the assignment of @MaxDT and its value in the row set output?
Once again just so you won't think implicit casting has anything to do with this:

Declare
@MinDT DateTime,
@MaxDT DateTime;

Select
@MinDT ='1753-01-01 00:00:00.000',
@MaxDT ='9999-12-31 23:59:59.998'

Select
@MinDT,
@MaxDT;

-- @MaxDT is now a valid DateTime value.
Set @MaxDT=DateAdd(ms,1,@MaxDT);

Select
@MinDT,
@MaxDT;

Both SELECT statement output the same results:

1753-01-01 00:00:00.000 | 9999-12-31 23:59:59.997

1753-01-01 00:00:00.000 | 9999-12-31 23:59:59.997

Since the accuracy of a DateTime data type is 0.00333 then the highest actual millisecond value would be 1.0-0.00333=0.99667 or 0.997 after rounding.
Explanation:
1.0-0.998=0.002, 0.998-0.9967=0.0013; hence 0.998 is closer to 0.9967 than it is to 1.0 so the rounded value is 0.997
Had the rounding gone the other way the assignment to @MaxDT would throw an "out-of-range datetime value" exception.

This leads us to our next two examples:

Declare
@MinDT DateTime,
@MaxDT DateTime;

Select
@MinDT ='1753-01-01 00:00:00.000',
@MaxDT ='9999-12-31 23:59:59.999'

Declare
@MinDT DateTime,
@MaxDT DateTime;

Select
@MinDT ='1753-01-01 00:00:00.000',
@MaxDT ='9999-12-31 23:59:59.997'

Set @MaxDT=DateAdd(ms,2,@MaxDT)

Now it should be more readily understood why both of these examples throw "out-of-range datetime value" exceptions even though the apparent values appear to be in range.

Promised weirdness...


Declare
@MinDT DateTime,
@MaxDT DateTime;

Select
@MinDT ='1753-01-01 00:00:00.000',
@MaxDT ='9999-12-31 23:59:59.997'

Select
@MinDT,
@MaxDT,
@MaxDT-@MinDT;

This code throws "out-of-range datetime value"! I don't know about you but this messed with my head. I would have thought that I would get the lowest DateTime value back, i.e., 1753-01-01 00:00:00.000. In fact, with a little testing I found that the following will work!

Declare
@MinDT DateTime,
@MaxDT DateTime;

Select
@MinDT ='1753-01-01 00:00:00.000',
@MaxDT ='9852-12-31 23:59:59.997'

Select
@MinDT,
@MaxDT,
@MaxDT-@MinDT;

Hmm, looks like we really don't have years 1753 - 9999 as a consistently valid range for all DateTime operations?!? In fact there is a 147 year discrepancy! A little more testing reveals:

Select GetDate()-GetDate();

Which returns: 1900-01-01 00:00:00.000 instead of 1753-01-01 00:00:00.000! Now I see what's going on here. The 147 years is the difference between 1900 and 1753! This is why some programmer's DateTime code will overflow sooner/later than other programmer's DateTime code; it depends on which functions you use to calculate the date and time differences. However, given that most of us probably won't be programming in Microsoft SQL (at least today's current versions!) in 9999, a 147 year discrepancy is probably not a problem.

Therefore, if ya just can't spare 147 years in your elapsed time functions don't use an algorithm that uses the binary subtract operator ("-") to get the DateTime difference; use DateDiff on DateTime values where the CCYY-MM-DD component has been edited to the same value, such as 1900-01-01 (taking into account any time range inversion).




PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1532336
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse