Using LAG To Find The Difference In Timestamps Between Rows

Hey everyone, tis another merry entry of SQL Something!

Slipping in this last post for 2019!

HAPPY NEW YEAR AND ALL THE BEST FOR THE UPCOMING 2020!

Today we are going to look at using LAG and specifically how to use it to find the difference between times, where one time value... IS IN THE PREVIOUS ROW.

Turns out it's really simple using LAG (available in SQL 2015 onwards).



Now, my issue came from a scenario where I had a table that recorded the start times and end times of how long a connection was active:

Fig. 1: Active times for a connection!

What I was tasked to find from this info was how long the connections were DOWN for. For that, I would need to subtract the "StartTime" from the previous row's "EndTime".

I wanted a result like:

Fig. 2: Connection downtime info!

Here, "Connection Downtime" is calculated by subtracting the previous row's EndTime from the current row's StartTime. I then broke it up into hours or minutes.

To do that I used the following:

SELECT
c.ConnectionID,
c.StartTime,
c.EndTime,
DATEDIFF(HOUR, c.StartTime, c.EndTime) AS 'Connection Duration (Hours)',

DATEDIFF(MINUTE,
LAG(c.EndTime, 1) OVER (ORDER BY c.ConnectionID),
c.EndTime) AS 'Connection Downtime (Minutes)',

DATEDIFF(HOUR,
LAG(c.EndTime, 1) OVER (ORDER BY c.ConnectionID),
c.EndTime) AS 'Connection Downtime (Hours)'
FROM
ConnectionTest c
ORDER BY
c.ID;


The above got me the results I wanted in Fig. 2.

With LAG, you can specofy you row offset, i.e. the number of row backwards you wish to access. I specified '1' and used that value in my DATEDIFF calculations.

And that is all to it. :-)

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