Using timestampdiff and getting the previous log of particular user

Question

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

I am looking for something like this

TIMESTAMPDIFF(NOW(), Previous_Log_of_User)

So, if for example I have 2 users and I have this 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 example above I have 2 users and the starting point is if the user logs in, if the users action is increment I will calculate the time between login and the increment action, then if the users action is increment again we will now calculate the duration between previous increment and the increment now and so on and so forth until the user logs out which will mark the end.

One of the problems that I encounter here is because the table is not consistent, I can't rely on user_id alone to get the previous log of particular user. There are cases that the previous id of user1 is an id of user2.

Question:

Is there an easy way to do this beside using timedifference? Or any other approach that I can take? I'm spending ample amount of time here.

Edit:

I am using MySQL and VB.NET.

My database is like this

Our focus here is the teller_log table which logs any actions of the teller_info, and this teller_info table gets the name of the user in user_info table by means of teller_id field both in user_info and teller_info.

Then we can create a log with combined information from user_info and teller_info by means of user_id field in user_info table.

What I am trying to achieve is

1. By means of this query I can easily get the duration of all logs. (For checking purposes)This means that the query should not have WHERE, though am not sure?
2. I want to get the duration of the latest log according to time, which means that if user1 increments then it will create a log, and in that log I will specify the duration.

Answer 1

For just user1:

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

Minutes since 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 what action occurred last, that gets more complex.

Answer 2

Your question reads a little ambiguously. I have 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's the 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, please update the question with example input and expected output.

To address the second question (here's 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);