Find largest gap between timestamps

Transact-SQL
1

Hi,
My tsql foo is weak and I'm looking for a little help. I am trying to find the largest gap between timesstamps for a column. As you can see in the small example below, I have five rows and I'm looking to find the difference in seconds between consecutive rows and then order them by the biggest gap descending.

msg_id msg_datetime
1 2015-12-02 06:25:05.467
2 2015-12-02 07:45:06.953
3 2015-12-02 07:51:45.627
4 2015-12-02 07:52:15.253
5 2015-12-02 07:59:46.153

Any help is appreciated.
Thanks,
rod

2

Which version of SQL Server are you on?

SELECT 
	DATEDIFF(SECOND, a.msg_datetime, b.msg_datetime) AS Diff,
	*
FROM
	Tbl AS a
	LEFT JOIN Tbl AS b 
		ON b.msg_id = a.msg_id + 1
ORDER BY
	Diff DESC;
1 Like
3

Hi,
It can range from any version from 2005 to 2012. This works great on 2008. I appreciate the help!
Thanks,
Rod

4

I wouldn't assume the msg_ids are 100% sequential with never a gap:

WITH cte_msgs AS (
    SELECT msg_datetime, ROW_NUMBER() OVER(ORDER BY msg_datetime) AS row_num
    FROM table_name
)
SELECT MAX(DATEDIFF(SECOND, m1.msg_datetime, m2.msg_datetime)) AS Max_TimeDiff_In_Seconds
FROM cte_msgs m1
INNER JOIN cte_msgs m2 ON m2.row_num = m1.row_num + 1
5
  • 1 Billion!