Getting the Time Difference Between Inserted Rows (Given a DateTime Column) - SQL Server 2008

Good day and welcome to SQL Something!

I'm particularly pleased to write this one as it's the first non-MiniPost I've written in a while. Huzzah!

Today we are going to look at how to get the time difference between the insertion of two rows, given a datetime column.


This is useful because sometimes for example you really wish to know just how often a certain activity may be taking place and the amount of time between each occurrence of said activity. For example, off the top of my head (so my example may actually be terrible), login attempts. If there is a continuous amount of login attempts occurring steadily one after the other, with time intervals that a person is incapable of, you may have a problem. At the very least, it's suspicious. Granted, if everything is set up properly the user in question could be locked out after a couple invalid tries, but it might still be good to know that the suspicious activity is taking place.

In my day to day work, this has become kind of useful lately but unfortunately I can't really use that as an example, so I do hope that someone else finds this helpful in some way.

Ok, enough preamble. Let's look at the query below:

WITH Time_CTE AS
(
        SELECT
                RowNum = ROW_NUMBER() OVER (ORDER BY T.id),
                T.TimeLogged
        FROM    
                Test_Table T
)
SELECT TOP 100
        Prev.TimeLogged AS PreviousTime,
        Time_CTE.TimeLogged,
        (DateDiff(MILLISECOND, Prev.TimeLogged, Time_CTE.TimeLogged)) AS TimeDiff
FROM    
        Time_CTE
        LEFT JOIN Time_CTE Prev
        ON Prev.RowNum = Time_CTE.RowNum - 1


The basis of this query can be found here. The original query posted found the previous value, but after a couple small changes it can find the time difference. This is a very simplified version using a test table. For your production needs you can tweak it to suit (I had to do some extensive changes/additions for what I needed it to do).

With regard to how it actually works:

• First it creates a Common Table Expression (kinda like a temporary table; the CTE part can actually be redone using a temp table) with a RowNum column that acts as a count for the current row. TimeLogged is my datetime column.
• The second half of the query then query's the CTE as well as joins with itself on the RowNum column. This is to find the previous row.
• We find the time Difference using the DateDiff function, and have the results in milliseconds (this can be changed to whatever suits your needs).

And that's more or less it. You will get results like below:

Fig. 1:
Note that the initial row has a NULL for the TimeDiff column. This is because there is no previous value for it to find the TimeDiff (indicated by a NULL in the PreviousTime column).

Please note that performance time will be pretty bad for large amounts of data, so take heed. As indicated in the original post there is a better way in SQL Server 2012, but I haven't tried it yet so I didn't post it. I will once I do and experience it first hand.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE (by all means!) feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)