Time difference with dates in same column

Question

Time difference with dates in same column

thomashohner

SSCrazy Eights

Points: 9856
More actions
March 17, 2015 at 3:05 pm

#295968

How do I find the time difference when the dates are in one column? I need to find hours and minutes between each row. I am on SQL server 2008 r2 so I'm out of luck with the new goodies in 2012.
Here is some ddl. Thanks for any suggestions or help
CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)
INSERT INTO #Time (TimeStamp)
VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')
, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')
DROP TABLE #Time
***SQL born on date Spring 2013:-)

Viewing 7 posts - 1 through 6 (of 6 total)

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

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 1

WITH TEMP_CTE AS(SELECT [TimeStamp], ROW_NUMBER() OVER(PARTITION BY (SELECT 1) ORDER BY [TimeStamp] ASC) AS ROW_NUM FROM #Time

)

SELECT T_ONE.[TimeStamp], T_TWO.[TimeStamp], DATEDIFF(minute, T_ONE.[TimeStamp], T_TWO.[TimeStamp]) FROM TEMP_CTE T_ONE, TEMP_CTE T_TWO

WHERE T_ONE.ROW_NUM = T_TWO.ROW_NUM - 1

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 2

Quick suggestion

😎

CREATE TABLE #Time ([TimeStamp] DATETIME, TimeDiff INT)

INSERT INTO #Time (TimeStamp)

VALUES ('2014-09-02 07:51:02.810'), ('2014-09-02 07:48:09.567'), ('2014-09-02 08:37:09.647')

, ('2014-09-02 16:16:42.593'), ('2014-09-02 08:06:13.387'),('2014-09-02 14:32:00.113')

;WITH BASE_DATA AS

(

SELECT

TS.[TimeStamp]

,ROW_NUMBER() OVER

(

ORDER BY TS.[TimeStamp]

) AS TS_RID

FROM #Time TS

)

SELECT

BD.[TimeStamp]

,CONVERT(TIME(0),DATEADD(MINUTE,DATEDIFF(MINUTE,BD2.[TimeStamp],BD.[TimeStamp]),CONVERT(DATETIME,0,0)),0) AS TimeDiff

FROM BASE_DATA BD

LEFT OUTER JOIN BASE_DATA BD2

ON BD.TS_RID = BD2.TS_RID + 1

DROP TABLE #Time

Results

TimeStamp TimeDiff

----------------------- ----------------

2014-09-02 07:48:09.567 NULL

2014-09-02 07:51:02.810 00:03:00

2014-09-02 08:06:13.387 00:15:00

2014-09-02 08:37:09.647 00:31:00

2014-09-02 14:32:00.113 05:55:00

2014-09-02 16:16:42.593 01:44:00

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 3

Wow that was fast!

And its faster than my attempt at creating a temp table to build a start and stop time column.

Amazing 242,139 rows in under 30 seconds

***SQL born on date Spring 2013:-)

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 4

thomashohner (3/17/2015)
Wow that was fast!
And its faster than my attempt at creating a temp table to build a start and stop time column.
Amazing 242,139 rows in under 30 seconds

Hence the "quick suggestion":-D

😎

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

thomashohner (3/17/2015)
Wow that was fast!
And its faster than my attempt at creating a temp table to build a start and stop time column.
Amazing 242,139 rows in under 30 seconds

How many columns and how wide is the typical row in this table?

--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)

thomashohner SSCrazy Eights Points: 9856 More actions · Answer 6

Hello Jeff,

Is this because of the possible performance issue with using a cte and a wide/large table/results?

The current query is not bad I am only using 15 columns and 8 of those are INT's and the rest are < Varchar(100) OR DATETIME.

I saw a blog where they claim a CURSOR is more efficient in this case. However I have not been able to bring myself to use one.

http://sqlperformance.com/2012/07/t-sql-queries/running-totals

***SQL born on date Spring 2013:-)