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 Saturday, January 18, 2014 12:40 AM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:36 AM
Points: 190, Visits: 442

More DateTime info...


This time I teased out the internal representation of the DateTime data type. First I ran the following:
Declare @d DateTime;
Set @d='1753-01-01 00:00:00.000';
Select Cast(@d as VarBinary),Cast(@d as BigInt);

And received:
0xFFFF2E4600000000 | -53690

Immediately I noticed that 0xFFFF2E4600000000 is not equivalent to -53690! In fact -53690 is the decimal representation of the first 4 hexadecimal bytes! The Cast to VarBinary seemed to know that @d was 8 bytes long, why didn't the Cast to BigInt have the same length info? So then I tried this:
Declare @d DateTime;
Set @d='1753-01-01 00:00:00.000';
Select Cast(@d as VarBinary),Cast(@d as BigInt),Cast(Cast(@d as VarBinary) as BigInt);

And received:
0xFFFF2E4600000000 | -53690 | -230596794122240

Now that's more like it. 0xFFFF2E4600000000 is equivalent to -230596794122240! Next, based on a hunch from a previous post on this thread I ran:
Declare @d DateTime;
Set @d='1900-01-01 00:00:00.000';
Select Cast(@d as VarBinary),Cast(@d as BigInt),Cast(Cast(@d as VarBinary) as BigInt);

And received:
0x0000000000000000| 0 | 0

It looks like the internal representation is broken down in the following manor:
Bytes 1 - 4 represent the date component (CCYY-MM-DD)
Bytes 5-8 represent the time component (HH:MM:SS.mmm)

But what are their units and intervals? Well, the following query will tell us that:
Declare @d DateTime;
Set @d='1753-01-01 00:00:00.000';
Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);
Set @d='1753-01-02 00:00:00.001';
Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);
Set @d='1753-01-02 00:00:00.003';
Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);

0xFFFF2E4600000000 | -53690 | 0
0xFFFF2E4700000000 | -53689 | 0
0xFFFF2E4700000001 | -53689 | 1

Notice how I modified the date-time string before each query and notice the resultant output values. From an analysis of these values it can be seen that:
Bytes 1 - 4 represent the date component in days, however, the range does not start with 0, it starts with -53690.
Bytes 5-8 represent the time component in 3.33~ millisecond counts, i.e., this count increments by 1 for every 3.33~ milliseconds that pass; the range starts at 0.

So what are the actual ranges for days and 3.33~ millisecond counts? Lets run another query:
Declare @d DateTime;
Set @d='1753-01-01 00:00:00.000';
Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);
Set @d='9999-12-31 23:59:59.997';
Select Cast(@d as VarBinary),Cast(SubString(Cast(@d as VarBinary),1,4) as Int),Cast(SubString(Cast(@d as VarBinary),5,4) as Int);

0xFFFF2E4600000000 | -53690 | 0
0x002D247F018B81FF | 2958463 | 25919999

From these results we can see that the day range is -53690 through 2958463, or 3012154 (don't forget to add 1 for the zero crossing) which is 8246 years, the full span from years 1753 to 9999.
From these results we can also see that the 3.33~ millisecond count range is 0 through 25919999, or 25920000 counts which is 86400000 milliseconds (25920000*3.33~) or 1 days worth of milliseconds.

So, there you have it. You could use this knowledge to craft an ElapsedTime function that will work across the full spectrum of time from 1753-01-01 00:00:00.000 through 9999-12-31 23:59:59.997 with some reasoned logic and judicious use of SubString, Cast, & Convert. It probably won't be the fastest performer but it will give you the full time span available without any "out-of-range datetime value" exceptions!

Happy Casting!




PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be.
Post #1532357
Posted Saturday, January 18, 2014 7:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:22 PM
Points: 2,532, Visits: 7,068
Jeff Moden (1/16/2014)
Eirikur Eiriksson (1/16/2014)
Thank you for the spackle Jeff but it looks like there is a small crack in it as it makes the assumption that the end time is always greater than the start time. This is the reason why I prefer to use double cast, first to varbinary and then to bigint.

SELECT 
StartDT
,EndDT
,Duration = STUFF(CONVERT(VARCHAR(20),EndDT-StartDT,114),1,2,DATEDIFF(hh,0,EndDT-StartDT))
,DurationI = STUFF(CONVERT(VARCHAR(20),StartDT-EndDT,114),1,2,DATEDIFF(hh,0,StartDT-EndDT))
,cast(cast(StartDT as varbinary(128)) as bigint) As StartTicks
,cast(cast(EndDT as varbinary(128)) as bigint) AS EndTicks
,cast(cast(StartDT as varbinary(128)) as bigint)-cast(cast(EndDT as varbinary(128)) as bigint) as XmY
,cast(cast(EndDT as varbinary(128)) as bigint)-cast(cast(StartDT as varbinary(128)) as bigint) as YmX
FROM #JBMTest;



You're correct and I could have added a simple statement, such as "Assuming two non-null dates where the StartDT is less than or equal to the EndDT,...", to make sure that no one would have to assume.

Shifting gears to the code you posted...

While I very much appreciate alternate methods for just about everything, I have to admit that the code you wrote for the StartTicks and EndTicks columns has me confused. Why are they labeled as "Ticks"? Because DATETIME is based on two integers where one integer represents the number of whole days and the other represents the number of 1/300ths of a second that have passed since midnight, I don't understand where the word "Ticks" comes into play. Without further explanation or esoteric knowledge, one might think that you were implying "clock ticks" and, without some additional calculation, they are not. Further, since the maximum hex value of the "Time" portion for a DATETIME datatype is 018B81FF (23:59:59.997) and falls drastically short of the FFFFFFFF value before the rollover to the next int (day), the VARBINARY/BIGINT converted value can't represent "ticks" of equal duration of any kind. A simple conversion of DATETIME to FLOAT would get you a lot closer to some form of "ticks" value.

This is the reason why I prefer to use double cast, first to varbinary and then to bigint.


I'm even more confused after that statement. Please explain how your code, as it is posted, ensures that the StartDT and EndDT values are in the proper order even if they were to produce the required human-readable output (and it IS required by most). From what I can see, the code doesn't resolve that problem. Unless I'm missing something, it simply presents two columns where an additional decision would still need to be made as to whether or not the two DATETIMEs were in the correct order or not.

And, of course, although most would never approach the limit and as you have yourself recognized, your conversions do have a limit at 148 years whereas the code in the article does not.

I must be missing something. Please explain.


First let me apologize for the inaccuracy and incompleteness of my initial postings and for implying that a direct cast of the DATETIME to BIGINT would hold any sensible meaning.

To finish what I started;
DATETIME is a structure of two integers, signed integer for the number of days starting at -53690 for 1753-01-01 which are the number of days until 1900-01-01.
For this reason, the subtraction of two equal datetime values returns 1900-01-01.
It can hold 3012153 days or 2958463 days after 1900-01-01 which is 9999-12-31.

And as you correctly said, the second part of the DATETIME structure holds the time from midnight in 1/300th parts of a second, that is 25920000 intervals per day. This makes 78,075,031,679,999 (1/300th) parts for the full day range of the DATETIME, well within the limits of the BIGINT, 2^63-1 (9,223,372,036,854,775,807). Since the storage size is the same for both data types, I prefer to store both date-time and duration/period as (1/300th) parts BIGINT if to be used for temporal calculations.

The calculation is quite quick and straight forward; extract the day part, add 53690, then multiply by 25920000 and add the time part. To extract the day value one can either shift the bytes by division of 0xFFFFFFFF or using SUBSTRING, the latter tends to be faster. For the time part OR or XOR with an integer of 0 (zero).

A sample test code snip using the #JBMTest;

DECLARE @BitBucket BIGINT;
SET STATISTICS TIME ON;
SELECT @BitBucket = ABS(
((CAST((53690 +
CAST(SUBSTRING(CAST(EndDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000)
+ (CAST(EndDT AS VARBINARY(8)) ^ 0)) -
((CAST((53690 +
CAST(SUBSTRING(CAST(StartDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000)
+ (CAST(StartDT AS VARBINARY(8)) ^ 0))
)
FROM #JBMTest;
SET STATISTICS TIME OFF;
GO


A MIN-MAX demonstration

DECLARE @MinDT DateTime;
DECLARE @MaxDT DateTime;

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

SELECT
@MinDT AS MinDateTime
,@MaxDT AS MaxDateTime
,ABS(
((CAST((53690 +
CAST(SUBSTRING(CAST(@MaxDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000)
+ (CAST(@MaxDT AS VARBINARY(8)) ^ 0)) -
((CAST((53690 +
CAST(SUBSTRING(CAST(@MinDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000)
+ (CAST(@MinDT AS VARBINARY(8)) ^ 0))
) AS TP_300
,ABS(
((CAST((53690 +
CAST(SUBSTRING(CAST(@MaxDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000)
+ (CAST(@MaxDT AS VARBINARY(8)) ^ 0)) -
((CAST((53690 +
CAST(SUBSTRING(CAST(@MinDT AS VARBINARY(8)),1,4) AS int)) AS BIGINT) * 25920000)
+ (CAST(@MinDT AS VARBINARY(8)) ^ 0))
) / 25920000 AS NumberOfDays
;


Again my apologies for the inaccuracy of my previous postings.
Post #1532376
Posted Monday, January 20, 2014 6:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 5:36 AM
Points: 2, Visits: 16
Thanks for the article. I appreciate it.
One question though. It said in the article
It’s important that the DATEDIFF in the formula have a "starting date" of "0" (the numeric equivalent of 1900-01-01) and an "ending date" of the duration calculation so that we don’t introduce any errors simply by crossing an "hour boundary" at the wrong time.
which I expect means the DATEDIFF section should use
DATEDIFF(hh,0,@EndDT-@StartDT)
rather than
DATEDIFF(hh,@StartDT,@EndDT)
which also returns 26 in this example. I guess I don't understand what an "hour boundary" is or why we need to guard against crossing one. Could you give an example where these two DATEDIFF statements would give different results?
Post #1532591
Posted Monday, January 20, 2014 8:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:14 AM
Points: 6,890, Visits: 14,253
When the hour increments from one value to the next, or from 12 to 1, an hour boundary is said to have been crossed. The same happens for the other time portions too. Try this code:

SELECT *,
[Hours] = DATEDIFF(hour, Starttime,EndTime),
[Minutes] = DATEDIFF(minute, Starttime,EndTime),
Seconds = DATEDIFF(second, Starttime,EndTime),
[millisecond] = DATEDIFF(millisecond, Starttime,EndTime),
[microsecond] = DATEDIFF(microsecond, Starttime,EndTime)
FROM (
SELECT
Starttime = cast('20140120 16:59:59.997' AS DATETIME),
EndTime = cast('20140120 17:00:00.000' AS DATETIME)
) d


There's only 3ms difference between Starttime and EndTime, but an hour boundary was crossed (16:nn:nn to 17:nn:nn), a minute boundary was crossed (16:59:nn to 17:00:nn) and a second boundary was crossed (16:59:59 to 17:00:00).



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1532669
Posted Monday, January 20, 2014 1:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 29, 2014 5:36 AM
Points: 2, Visits: 16
Thanks Chris, that helps a lot!

So I'm understanding now that starting at 0 and ending at the difference [ie. DATEDIFF(hh,0,@EndDT-@StartDT)] allows the query to work when the hour part of @StartDT is larger than the hour part of @EndDT.
Post #1532778
Posted Monday, January 20, 2014 6:14 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:36 AM
Points: 190, Visits: 442

DateDiff tricks and the missing 147 years


Data-type DateTime has the following properties:

It can represent any valid date/time between 1753-01-01 00:00:00.000 and 9999-12-31 23:9:59.998.

Declare
@st DateTime,
@et DateTime;
Select
@st='1753-01-01 00:00:00.000',
@et='9999-12-31 23:59:59.998';

Mess with the dates a bit an you will see that these two are the upper and lower boundaries.
Nothing new here, BOL tells us that.

It is internally represented by two 32-bit integers.

Declare
@st DateTime,
@et DateTime;
Select
@st='1753-01-01 00:00:00.000',
@et='9999-12-31 23:59:59.997';
Select
Cast(@st as VarBinary)[@st as Hex],
Cast(@et as VarBinary)[@et as Hex];
Select
SubString(Cast(@st as VarBinary),1,4)[@st 1st Int as Hex],
SubString(Cast(@st as VarBinary),5,4)[@st 2nd Int as Hex],
SubString(Cast(@et as VarBinary),1,4)[@et 1st Int as Hex],
SubString(Cast(@et as VarBinary),5,4)[@et 2nd Int as Hex]
Select
Cast(SubString(Cast(@st as VarBinary),1,4) as Int)[@st 1st Int as Int],
Cast(SubString(Cast(@st as VarBinary),5,4) as Int)[@st 2nd Int as Int],
Cast(SubString(Cast(@et as VarBinary),1,4) as Int)[@et 1st Int as Int],
Cast(SubString(Cast(@et as VarBinary),5,4) as Int)[@et 2nd Int as Int];

Oddly, the lowest date/time boundary (1753-01-01 00:00:00.00) is not internally code as two zeros. It is -53690 and 0. Also, notice that the highest date/time first integer is 2958463. In passing the second integer really has no bearing on my subject but I'll tell you that it stores the number of 3.33~ millisecond ticks since midnight for a given day.

Now run this:

Select
DateAdd(dd,53690,'1753-01-01 00:00:00.000');

And we get 1900-01-01 00:00:00.000. This is our reference point around which the first of the two internal integers is composed; it represent the arithmetic difference in days between 1900-01-01 00:00:00.000 and your other date/time value. This is why, internally, it can be negative or positive. This is also the source of another odd T-SQL quirk (like this is the first one).

Declare
@st DateTime,
@et DateTime;
Select
@st='1753-01-01 00:00:00.000',
@et='9999-12-31 23:59:59.998';
Select
DateDiff(yy,@st,@et);

You get back 8246. No surprises here, this is just the number of years between our valid lowest and highest dates. Now run:

Declare
@st DateTime,
@et DateTime;
Select
@st='1753-01-01 00:00:00.000',
@et='9999-12-31 23:59:59.998';
Select
DateDiff(yy,0,@et-@st);

Huh, the dreaded Arithmetic overflow error converting expression to data type datetime. But if you run:

Declare
@st DateTime,
@et DateTime;
Select
@st='1900-12-31 00:00:00.000',
@et='9999-12-31 23:59:59.998';
Select
DateDiff(yy,0,@et-@st);

Everything is ok, you get 8099, the number of years between those two date/time values.

So what happened here that we apparently lost 147 years of range (1900-1753) by using the DateDiff function in this manor? Well, the DateDiff function wants a DateTime value as it's second and third parameters so it can do the difference calculation with the knowledge of its internal days representation. When you pass 0 as the second parameter you are thwarting this process by calculating the difference yourself and possibly getting caught by the zero crossing problem!

Example:

1753-01-01 is internally represented by a days number of -53690 (the smallest valid days number)

1900-01-01 is internally represented by a days number of 0

9999-01-01 is internally represented by a days number of 2958463 (the largest valid days number)

Ex.1) If you take the arithmetic difference (using the binary minus operator) between 9999-01-01 and 1900-01-01 as days you'd get 2958099

Ex.2) If you take the arithmetic difference (using the binary minus operator) between 9999-01-01 and 1753-01-01 as days you'd get 3012153

Now, I'm going to tell you that this problem actually has nothing to do directly with the DateDiff function! When you take the arithmetic difference of two DateTime values the result is implicitly cast back into a DateTime value. Therefore, Ex.1 above would have no problem being passed into DateDiff because it will cast back into a DateTime data type. However, Ex.2 runs into the Arithmetic overflow error converting expression to data type datetime because 3012153 is larger than the highest internal days number!

So, we can see that DateDiff knows how to handle the difference calculation when one of its DateTime values has a negative representation, where an arithmetic difference doesn't! So, should we avoid using DateDiff in this manor? Of course not! Its solves some rounding problems for us when we do! Just use it with an awareness of what is going on under the covers!

Your 147 years have been returned!




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

Add to briefcase «««12345

Permissions Expand / Collapse