Time as a high precision difference of dates

Question

Time as a high precision difference of dates

Viewing 7 posts - 31 through 36 (of 36 total)

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

ScottPletcher SSC Guru Points: 101120 More actions · Answer 1

bmahf (9/6/2012)
So it turns out that each of these solutions is missing something, it's either the fraction part sometimes comes out incorrect, because it wasn't adjusted as I was doing in my case statement, or the seconds were not adjusted, which I also did in the other case statement I sent. I also found that the case where I adjusted the fraction part because it was negative still had another issue, which was that it was never adjusted for position.
So since this is for a one-time converter, which doesn't have to be optimized to the extreme, I rewrote my version, and made it into a function, and I call that function inside my select. My version now looks like this, and I haven't found any problems with it yet:
But, I must say it is interested to try to figure out the optimized version. It's just that the possible adjustment and the positioning of the fractional part, and the possible adjustment of the seconds part need to be taken into account.
Thanks for all the help.

I don't see those issues with my code, but I too did not do an exhaustive check against all possible values 🙂 .

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Paul White SSC Guru Points: 150468 More actions · Answer 2

Erland has a Connect request that seems relevant to this discussion:

http://connect.microsoft.com/SQLServer/feedback/details/320998/add-datediff-big

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ScottPletcher (9/6/2012)
bmahf (9/6/2012)
So it turns out that each of these solutions is missing something, it's either the fraction part sometimes comes out incorrect, because it wasn't adjusted as I was doing in my case statement, or the seconds were not adjusted, which I also did in the other case statement I sent. I also found that the case where I adjusted the fraction part because it was negative still had another issue, which was that it was never adjusted for position.
So since this is for a one-time converter, which doesn't have to be optimized to the extreme, I rewrote my version, and made it into a function, and I call that function inside my select. My version now looks like this, and I haven't found any problems with it yet:
But, I must say it is interested to try to figure out the optimized version. It's just that the possible adjustment and the positioning of the fractional part, and the possible adjustment of the seconds part need to be taken into account.
Thanks for all the help.
I don't see those issues with my code, but I too did not do an exhaustive check against all possible values 🙂 .

Your code is spot on, Scott, with all the values we've tested so far:

;With MyCTE (RowID, aTime, bTime)

AS

(

SELECT 1, CONVERT(datetime2,'1900-01-01 00:08:00.0000001'), CONVERT(datetime2,'1900-01-01 00:09:18.3610010')

UNION ALL SELECT 2, '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705002'

UNION ALL SELECT 3, '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'

UNION ALL SELECT 4, '1900-01-01 23:09:18.361', '1900-01-02 02:13:50.7050001'

UNION ALL SELECT 5, '1900-01-01 00:00:00.000000111', '1900-01-01 00:00:00.000000222'

UNION ALL SELECT 6, '1900-01-01 00:00:00.000000222', '1900-01-01 00:00:00.000000111'

UNION ALL SELECT 7, '1900-01-01 00:10:00.000000111', '1900-01-01 00:00:00.000000222'

UNION ALL SELECT 8, '1900-01-01 00:10:00.999998811', '1900-01-01 00:10:01.000002222'

UNION ALL SELECT 9, '1900-01-01 00:10:01.999998811', '1900-01-01 00:10:00.000002222'

UNION ALL SELECT 10, '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705'

UNION ALL SELECT 11, '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'

UNION ALL SELECT 12, '1900-01-01 23:09:18.361', '1900-01-02 02:13:50.7050001'

UNION ALL SELECT 13, '1900-01-01 00:00:00.0000001', '1900-01-01 00:00:00.0000002'

UNION ALL SELECT 14, '1900-01-01 00:00:00.0000002', '1900-01-01 00:00:00.0000001'

UNION ALL SELECT 15, '1900-01-01 00:10:00.0000001', '1900-01-01 00:00:00.0000002'

UNION ALL SELECT 16, '1900-01-01 00:10:01.9999988', '1900-01-01 00:10:00.0000022'

UNION ALL SELECT 17, '1900-01-01 00:10:00.9999998', '1900-01-01 00:10:01.0000001'

UNION ALL SELECT 18, '1900-01-01 00:10:00.0000023', '1900-01-01 00:10:01.0000022'

UNION ALL SELECT 19, '1900-01-01 00:10:00.0000022', '1900-01-01 00:10:01.0000022'

UNION ALL SELECT 20, '1900-01-01 00:10:00.0000021', '1900-01-01 00:10:01.0000022'

UNION ALL SELECT 21, '1900-01-01 00:10:00.0000020', '1900-01-01 00:10:01.0000022'

)

SELECT

RowID,

aTime,

bTime,

SimpleProof = CASE WHEN ABS(DATEDIFF(ss,aTime,bTime)) < 11072

THEN CAST(DATEADD(microsecond,ABS(DATEDIFF(microsecond,aTime, bTime)),CAST('1900' AS DATETIME2)) AS TIME(7))

ELSE CAST(DATEADD(millisecond,ABS(DATEDIFF(millisecond,aTime, bTime)),CAST('1900' AS DATETIME2)) AS TIME(7)) END,

TimeResult1 = -- ChrisR (5 date functions, 2 datatype conversions)

CAST(

DATEADD(nanosecond,

CASE

WHEN Sec = 0 THEN ABS(NanoSec)

WHEN Sec < 0 THEN NanoSec * -1

ELSE NanoSec

END,

DATEADD(second,ABS(Sec),CAST('1900-01-01 00:00' AS datetime2))

) AS TIME(7)),

TimeResult2 = -- ChrisM (5 date functions, 2 datatype conversions)

CAST(

DATEADD(ns,(DATEPART(ns,bTime)-DATEPART(ns,aTime)) * (CASE WHEN aTime < bTime THEN 1 ELSE -1 END),

DATEADD(ss,ABS(DATEDIFF(ss,aTime,bTime)),CAST('1900' AS datetime2))

) AS TIME(7)),

TimeDiff = -- ScottP (8 date functions, 3 datatype conversions)

CAST(

CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, StartTime, EndTime)

- CASE WHEN DATEPART(NANOSECOND, StartTime) > DATEPART(NANOSECOND, EndTime) THEN 1 ELSE 0 END, 0), 108) +

'.' + RIGHT(REPLICATE('0', 7) +

CAST(((CASE WHEN DATEPART(NANOSECOND, StartTime) > DATEPART(NANOSECOND, EndTime) THEN 1000000000 ELSE 0 END +

DATEPART(NANOSECOND, EndTime) - DATEPART(NANOSECOND, StartTime)) / 100) AS varchar(7)), 7)

AS TIME(7)),

TimeDiff = -- Steven W (5 date functions, 10 datatype conversions)

-- fails with datetime conversion error on rows 3,4,7,9,11,12,15,16,18

-- returns incorrect result for rows 8 & 17 using starttime & endtime

-- returns incorrect result for rows 6,8,14 & 17 using atime & btime

CAST(RIGHT('00' + CONVERT(VARCHAR,DATEDIFF(hour,aTime,bTime) % 24),2)

+ ':' + RIGHT('00' + CONVERT(VARCHAR,DATEDIFF(minute,aTime,bTime) % 60),2)

+ ':' + RIGHT('00' + CONVERT(VARCHAR,(DATEDIFF(second,aTime,bTime)) % 60),2)

+ '.' + RIGHT('0000000' + CONVERT(VARCHAR,(CAST((CAST(CAST(DATEPART(nanosecond,EndTime) AS INT) AS DECIMAL(18,8))/100)

- (CAST(CAST(DATEPART(nanosecond,aTime) AS INT) AS DECIMAL(18,8))/100) AS INT))),7)

AS TIME)

From (

SELECT

RowID,

Sec = (DATEDIFF(second, aTime, btime)),

NanoSec = (DATEPART(ns,btime)-DATEPART(ns,atime)),

aTime,

bTime,

StartTime = CASE WHEN aTime < bTime THEN aTime ELSE bTime END,

EndTime = CASE WHEN aTime < bTime THEN bTime ELSE aTime END

FROM MyCTE

) d

--WHERE RowID NOT IN (3,4,7,9,11,12,15,16,18) -- rows which may fail

^{“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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

bmahf (9/6/2012)
So it turns out that each of these solutions is missing something, it's either the fraction part sometimes comes out incorrect, because it wasn't adjusted as I was doing in my case statement, or the seconds were not adjusted, which I also did in the other case statement I sent. I also found that the case where I adjusted the fraction part because it was negative still had another issue, which was that it was never adjusted for position.
So since this is for a one-time converter, which doesn't have to be optimized to the extreme, I rewrote my version, and made it into a function, and I call that function inside my select. My version now looks like this, and I haven't found any problems with it yet:
But, I must say it is interested to try to figure out the optimized version. It's just that the possible adjustment and the positioning of the fractional part, and the possible adjustment of the seconds part need to be taken into account.
Thanks for all the help.

Interested to see your new version, Bruce.

^{“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

ScottPletcher SSC Guru Points: 101120 More actions · Answer 5

TimeResult1 = -- ChrisR (5 date functions, 2 datatype conversions)
TimeResult2 = -- ChrisM (5 date functions, 2 datatype conversions)
TimeDiff = -- ScottP (8 date functions, 3 datatype conversions)
TimeDiff = -- Steven W (5 date functions, 10 datatype conversions)

These totals aren't quite accurate.

My code uses only columns native to the table.

All other code use derived column that require pre-computing.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Paul White SSC Guru Points: 150468 More actions · Answer 6

ScottPletcher (9/7/2012)
TimeResult1 = -- ChrisR (5 date functions, 2 datatype conversions)
TimeResult2 = -- ChrisM (5 date functions, 2 datatype conversions)
TimeDiff = -- ScottP (8 date functions, 3 datatype conversions)
TimeDiff = -- Steven W (5 date functions, 10 datatype conversions)
These totals aren't quite accurate.
My code uses only columns native to the table.
All other code use derived column that require pre-computing.

Looking at the execution plans for:

With MyCTE (ID, aTime, bTime)

AS

(

SELECT 1, CONVERT(datetime2,'1900-01-01 00:08:00.0000001'), CONVERT(datetime2,'1900-01-01 00:09:18.361')

UNION ALL SELECT 2, '1900-01-01 00:08:00.0000001', '1900-01-01 00:13:50.705'

UNION ALL SELECT 3, '1900-01-01 00:09:18.361', '1900-01-01 03:13:50.7050001'

UNION ALL SELECT 4, '1900-01-01 23:09:18.361', '1900-01-02 02:13:50.7050001'

UNION ALL SELECT 5, '1900-01-01 00:00:00.0000001', '1900-01-01 00:00:00.0000002'

UNION ALL SELECT 6, '1900-01-01 00:00:00.0000002', '1900-01-01 00:00:00.0000001'

UNION ALL SELECT 7, '1900-01-01 00:10:00.0000001', '1900-01-01 00:00:00.0000002'

UNION ALL SELECT 8, '1900-01-01 00:10:01.9999988', '1900-01-01 00:10:00.0000022'

UNION ALL SELECT 9, '1900-01-01 00:10:00.9999998', '1900-01-01 00:10:01.0000001'

UNION ALL SELECT 10, '1900-01-01 00:10:00.0000023', '1900-01-01 00:10:01.0000022'

UNION ALL SELECT 11, '1900-01-01 00:10:00.0000022', '1900-01-01 00:10:01.0000022'

UNION ALL SELECT 12, '1900-01-01 00:10:00.0000021', '1900-01-01 00:10:01.0000022'

)

SELECT

aTime,

bTime,

TimeDiff = -- ScottP

CONVERT(varchar(8), DATEADD(SECOND, DATEDIFF(SECOND, aTime , bTime)

- CASE WHEN DATEPART(NANOSECOND, aTime) > DATEPART(NANOSECOND, bTime) THEN 1 ELSE 0 END, 0), 108) +

'.' + RIGHT(REPLICATE('0', 7) +

CAST(((CASE WHEN DATEPART(NANOSECOND, aTime) > DATEPART(NANOSECOND, bTime) THEN 1000000000 ELSE 0 END +

DATEPART(NANOSECOND, bTime) - DATEPART(NANOSECOND, aTime)) / 100) AS varchar(7)), 7)

FROM MyCTE;