Using timestampdiff and getting the previous log of a specific user

Question

I want to get the difference between 2 days, where datetime 1 is now() , and datetime 2 is the previous log of a specific user.

I'm looking for something like this

 TIMESTAMPDIFF(NOW(), Previous_Log_of_User) 

So, if, for example, I have 2 users, and I have a teller_log table

name timestamp action duration

user1 2015-09-09 15:00:40 login 00:00:00

user1 2015-09-09 15:00:45 increment 00:00:05

user1 2015-09-09 15:00:52 increment 00:00:07

user2 2015-09-09 15:00:52 login 00:00:00

user1 2015-09-09 15: 00: 55` increment 00:00:03

user2 2015-09-09 15:00: 58` increment 00:00:06

user1 2015-09-09 15: 01: 00` logout 00:00:02

user2 2015-09-09 15: 01: 00` logout 00:00:02

As you can see in the above example, I have 2 users, and the starting point is if the user logs in , if the user action is an increment, I calculated the time between login and increment action, then if the user action increases again, we now calculate the duration between the previous increment and the increment now, etc. etc., until the user logs out, which will mark the end.

One of the problems that I am encountering here is that the table is incompatible, I cannot rely only on user_id to get the previous log of a specific user. There are times when the previous userid1 is the userid2.

Question:

Is there an easy way to do this besides using timedifference? Or any other approach I can take? I spend enough time here.

Edit:

I am using MySQL and VB.NET .

My database is like

Our task is the teller_log table, which logs any teller_info actions, and this teller_info table gets the username in the user_info table using the teller_id field as in user_info > and teller_info .

Then we can create a log with combined information from user_info and teller_info using the user_id field in the user_info table.

What I'm trying to achieve

• With this query, I can easily get the duration of all the logs. (For verification purposes) Does this mean that the query should not have WHERE , although not sure?
• I want to get the duration of the last log depending on the time, which means that if user1 increases, he will create a log, and in this log I will indicate the duration.

Answer 1

Only for user1 :

 SELECT TIMESTAMPDIFF(HOUR, MAX(timestamp), NOW()) AS "Hours since last action" FROM tbl WHERE name = 'user1'; 

Minutes, as each user did something:

 SELECT name, TIMESTAMPDIFF(MINUTE, MAX(timestamp), NOW()) AS "Minutes since last action" FROM tbl GROUP BY name; 

If you want to know which action happened last, it becomes more complex.

Answer 2

Your question is read a bit ambiguously. I interpreted this as one of two ways: "For each action I want to know the elapsed time between it and" now ", or" For each action I want to know the elapsed time since the previous action. "

Based on the above interpretations, try the following (here SqlFiddle )

 select name, actionTimestamp, actionName, prevTimestamp , TIMESTAMPDIFF(second, actionTimestamp, NOW()) as elapsedTimeSinceAction , TIMESTAMPDIFF(second, prevTimestamp, actionTimestamp) as timeBetweenActions FROM (select *, (SELECT MAX(log2.actionTimestamp) FROM UserLog log2 WHERE log2.name = log1.name AND log2.actionTimestamp < log1.actionTimestamp) as prevTimestamp FROM UserLog log1 ) a ORDER BY name, actionTimestamp DESC, prevTimestamp DESC; 

If I misinterpreted your question, update the question with input and expected output.

To answer the second question (here is another SqlFiddle ):

 /* Gets the most recent action for each user. Note that if the same user performs multiple actions with the exact same timestamp, multiple records may be returned for that user. */ select log2.name, log2.actionName, log2.actionTimestamp , TIMESTAMPDIFF(second, log2.actionTimestamp, NOW()) as elapsedTimeSinceAction FROM (SELECT name, MAX(actionTimestamp) actionTimestamp FROM UserLog GROUP BY name) latestUserTimestamp JOIN UserLog log2 ON log2.name = latestUserTimestamp.name AND log2.actionTimestamp = latestUserTimestamp.actionTimestamp; /* Gets the most recent action for the user that performed the most recent action in the system. Note that if two (or more) users perform an action with the exact same timestamp, multiple users will be returned. */ SELECT *, TIMESTAMPDIFF(second, actionTimestamp, NOW()) as elapsedTimeSinceAction FROM UserLog WHERE actionTimestamp = (SELECT MAX(actionTimestamp) FROM UserLog); 

Answer 3

I think there are many ways to do this. If you don't want to do all the calculations in an SQL statement, you can simply query all the data in a datatable and then do something like this:

  Dim dt As New DataTable dt.Columns.Add("name") dt.Columns.Add("timestamp") dt.Columns.Add("action") dt.Columns.Add("duration") dt.Rows.Add("user1", "2015-09-09 15:00:40", "login", "00:00:00") dt.Rows.Add("user1", "2015-09-09 15:00:45", "increment", "00:00:05") dt.Rows.Add("user1", "2015-09-09 15:00:52", "increment", "00:00:07") dt.Rows.Add("user2", "2015-09-09 15:00:52", "login", "00:00:00") dt.Rows.Add("user1", "2015-09-09 15:00:55", "increment", "00:00:03") dt.Rows.Add("user2", "2015-09-09 15:00:58", "increment", "00:00:06") dt.Rows.Add("user1", "2015-09-09 15:01:00", "logout", "00:00:02") dt.Rows.Add("user2", "2015-09-09 15:01:00", "logout", "00:00:02") dt.AcceptChanges() Dim distinctDT As DataTable = dt.DefaultView.ToTable(True, "name") Dim name As String = "" Dim action As String = "" Dim previousAction As String = "" Dim initialDate As DateTime = Now Dim finalDate As DateTime = Now For j As Integer = 0 To distinctDT.Rows.Count - 1 name = distinctDT.Rows(j)("name") For i As Integer = 0 To dt.Rows.Count - 1 If name = dt.Rows(i)("name") Then action = dt.Rows(i)("action") If action = "login" Then initialDate = CDate(dt.Rows(i)("timestamp")) previousAction = action ElseIf action = "logout" Then previousAction = action finalDate = CDate(dt.Rows(i)("timestamp")) End If End If Next MsgBox(name & " => " & DateDiff(DateInterval.Second, initialDate, finalDate), vbInformation) Next 

I'm not sure if I understood correctly, but you only need actions: login and logout to calculate the duration, and in addition, you should make some minor changes to the code shown taking into account situations when you do not have an input to the system of actions before logging out or the absence of a logout action during an existing log file. It is for this reason that I considered the variable previousAction, without even using it in the code above.

Hope I can help something.