March 17, 2015 at 3:05 pm
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:-)
March 17, 2015 at 3:27 pm
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
March 17, 2015 at 3:27 pm
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
March 17, 2015 at 3:38 pm
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:-)
March 17, 2015 at 4:05 pm
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
March 17, 2015 at 5:59 pm
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
Change is inevitable... Change for the better is not.
March 18, 2015 at 8:03 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy