Time difference with dates in same column
-
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:-)
-
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_TWOWHERE T_ONE.ROW_NUM = T_TWO.ROW_NUM - 1 -
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(SELECTTS.[TimeStamp],ROW_NUMBER() OVER(ORDER BY TS.[TimeStamp]) AS TS_RIDFROM #Time TS)SELECTBD.[TimeStamp],CONVERT(TIME(0),DATEADD(MINUTE,DATEDIFF(MINUTE,BD2.[TimeStamp],BD.[TimeStamp]),CONVERT(DATETIME,0,0)),0) AS TimeDiffFROM BASE_DATA BDLEFT OUTER JOIN BASE_DATA BD2ON BD.TS_RID = BD2.TS_RID + 1DROP TABLE #TimeResults
TimeStamp TimeDiff----------------------- ----------------2014-09-02 07:48:09.567 NULL2014-09-02 07:51:02.810 00:03:002014-09-02 08:06:13.387 00:15:002014-09-02 08:37:09.647 00:31:002014-09-02 14:32:00.113 05:55:002014-09-02 16:16:42.593 01:44:00 -
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:-)
-
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
-
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) -
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:-)
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply