Calculating time difference between two logins in SQL Server

Question

Dear All,

I want to calculate the time spent by user on our system. In logs table we track currently login time but we don't have logout time logged currently. So to find out time spent by user in a session I want to find out difference between two login events

For example user has logged in multiple times on 2022-02-16 so I have to calculate the time difference between two login events. How we can achieve in that SQL Server? I have to do this inside a view, so can not use any temp tables or SP

Answer 1

Hi @jahanzaib rahman
Check this:

 ;WITH CTE AS  
 (  
  SELECT AccountId,UtcActionDate AS [Login Start],  
         LEAD(UtcActionDate,1,null)OVER(PARTITION BY AccountId ORDER BY UtcActionDate) [Login End],ActionType  
  FROM #logstable  
  WHERE (ActionType='Login' AND ActionSubType='LoginSuccess' ) OR (ActionType='Logout'AND ActionSubType='LogoutSuccess' )  
 )  
 SELECT AccountId,[Login Start],[Login End],  
        CAST((DATEPART(HOUR,[Login End]) - DATEPART(HOUR,[Login Start]))AS VARCHAR)+' h '+CAST((DATEPART(MI,[Login End]) - DATEPART(MI,[Login Start]))AS VARCHAR)+' m ' AS TimeSpent  
 FROM CTE  
 WHERE ActionType='Login'  

However, as Erland said, the query above only make sence when login and logout are arranged strictly one by another.
To avoid this issue,you could try this query:

 ;WITH CTE1 AS  
 (  
  SELECT *,LAG(ActionType,1,'Logout')OVER(PARTITION BY AccountId ORDER BY UtcActionDate) PreActionType,  
           LEAD(ActionType,1,'Login')OVER(PARTITION BY AccountId ORDER BY UtcActionDate) NextActionType  
  FROM #logstable  
 ),CTE2 AS(  
  SELECT *,LEAD(UtcActionDate)OVER(PARTITION BY AccountId ORDER BY UtcActionDate)AS [Login End]  
  FROM CTE1   
  WHERE (ActionType='Login' AND ActionSubType='LoginSuccess' AND PreActionType='Logout')   
     OR (ActionType='Logout' AND ActionSubType='LogoutSuccess' AND NextActionType='Login')  
 )  
 SELECT AccountId,UtcActionDate AS [Login Start],[Login End],  
        CAST((DATEPART(HOUR,[Login End]) - DATEPART(HOUR,UtcActionDate))AS VARCHAR)+' h '+CAST((DATEPART(MI,[Login End]) - DATEPART(MI,UtcActionDate))AS VARCHAR)+' m ' AS TimeSpent  
 FROM CTE2  
 WHERE ActionType='Login'  

Best regards,
LiHong

Answer 2

Hi,

I want to calculate the time spent by user on our system.

If your system is an image then neither you or we can calculate anything :-(

Please provide queries to create the table and insert sample data

In addition please provide the expected result set according to the sample data

---

In the meantime, check if the following example solve your need

use tempdb
GO

create table T(userid int, LoginTime datetime2)
GO

INSERT T(userid, LoginTime) values 
    (1,'2022-02-02T11:23:34'),
    (1,'2022-02-02T15:25:11'),
    (2,'2022-02-02T05:11:36'),
    (1,'2022-02-02T17:22:45'),
    (2,'2022-03-02T11:23:34'),
    (2,'2022-03-02T13:23:34')
GO

select userid, LoginTime, 
    DiffrentInTime_Minutes = DATEDIFF(HOUR, LoginTime, LEAD(LoginTime, 1,null) OVER (PARTITION BY userid ORDER BY LoginTime))
from T
GO

Answer 3

Hi @jahanzaib rahman
According to the information you have provided so far, I can only say that you need to use DATEDIFF function along with LAG/LEAD function.

SELECT AccountId,UtcActionDate,DATEDIFF(MI, LAG(UtcActionDate) OVER (PARTITION BY AccountId ORDER BY UtcActionDate), UtcActionDate) as Diff_Time  
FROM  logs_table  
WHERE ActionType = 'Login'  

Or not using LGA/LEAD like this:

;WITH CTE AS  
(SELECT  *, ROW_NUMBER() OVER (PARTITION BY AccountId ORDER BY UtcActionDate) AS rn  
 FROM logs_table   
)  
SELECT  DATEDIFF(MI, mc.UtcActionDate, mp.UtcActionDate)  
FROM  CTE mc JOIN CTE mp ON mc.rn = mp.rn - 1  

Also,here is an article which may be helpful to you:How to Calculate the Difference Between Two Rows in SQL
There are still some details that I'm not quite sure about.
For example, the time difference is calculated in minutes or seconds? Calculate the time difference between two consecutive logins, or calculate the time difference between the earliest and latest logins each day?etc.
So,could you please share us some sample data(CREATE TABLE… INSERT INTO …) along with your expected result? So that we’ll get a right direction and make some test.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

but we don't have logout time logged currently

What you don't have, you can't query.

For everything else, that's to less on information.
Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 5

Thank you everyone for the response and my apologies for not providing full information. Below is the create table statement

CREATE TABLE [logs-table](
[AccountId] [uniqueidentifier] NULL,
[UtcActionDate] [datetime] NULL,
[ActionType] nvarchar NULL,
[ActionSubType] nvarchar NULL
)

INSERT INTO logs-table(AccountId,UtcActionDate,ActionType,ActionSubType)
VALUES ('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:12:56.000', 'Login','LoginSuccess'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:22.000', 'Exchange','Start'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:55:27.000', 'Exchange','Start'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 16:56:27.000', 'Exchange','Start'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 20:48:06.000', 'Login','LoginSuccess'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 20:50:06.000', 'Exchange','Start'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-15 20:51:06.000', 'Exchange','Start'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-17 18:07:54.000', 'Login','LoginSuccess'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662','2022-02-17 18:08:54.000', 'Exchange','Start'),
('7DADAEDB-A0E5-4290-8D94-8D3C8144A662',2022-02-17 18:09:56.000', 'Exchange','Start'),

Now I want to find out how much time a user has spent in a session in one day. User can have multiple Login entries but I have to find out how much time difference (total hours and minutes )between each login
Sample output will look like

AccoundId Login Start Login End Time spent
7DADAEDB-A0E5-4290-8D94-8D3C8144A662 2022-02-15 16:12:56.000 2022-02-15 16:56:27.000 0h44m
7DADAEDB-A0E5-4290-8D94-8D3C8144A662 2022-02-15 20:48:06.000 2022-02-15 20:51:06.000 0h3m
7DADAEDB-A0E5-4290-8D94-8D3C8144A662 2022-02-17 18:07:54.000 2022-02-17 18:09:56.000 0h2m