MySQL Date difference between two rows

Question

I have a TABLE with Columns: USER_ID,TIMESTAMP and ACTION

Every row tells me which user did what action at a certain time-stamp.

Example:

• Alice starts the application at 2014-06-12 16:37:46
• Alice stops the application at 2014-06-12 17:48:55

I want a list of users with the time difference between the first row in which they start the application and the last row in which they close it.

Here is how I'm trying to do it:

    SELECT USER_ID,DATEDIFF(
(SELECT timestamp FROM MOBILE_LOG WHERE ACTION="START_APP" AND USER_ID="Alice"  order by TIMESTAMP LIMIT 1),
(SELECT timestamp FROM MOBILE_LOG WHERE ACTION ="CLOSE_APP" AND USER_ID="Alice"  order by TIMESTAMP LIMIT 1)
) AS Duration FROM MOBILE_LOG AS t WHERE USER_ID="Alice";

I ask for the DATEDIFF between two SELECT queries, but I just get a list of Alice`s with -2 as Duration.

Am i on the right track?

Answer 1

I think you should group this table by USER_ID and find minimum date of "START_APP" and maximum of "CLOSE_APP" for each user. Also you should use in DATEDIFF the CLOSE_APP time first and then START_APP time in this case you will get positive value result

SELECT USER_ID,
       DATEDIFF(MAX(CASE WHEN ACTION="CLOSE_APP" THEN timestamp END),
                MIN(CASE WHEN ACTION="START_APP" THEN timestamp END)
               ) AS Duration 
FROM MOBILE_LOG AS t 
GROUP BY USER_ID

SQLFiddle demo