CODEWITHSUNDEEP
mysqlsqlrowdatediff
Mark
Mark

Reputation: 1029

MySQL Date difference between two rows

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:

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?

Upvotes: 3

Views: 6897

Answers (3)

neshkeev
neshkeev

Reputation: 6486

You have the int value because you use the function DATEDIFF, it shows you the number of days between two dates, if you want to have the number of hours and minutes and seconds between dates you have to use TIMEDIFF Try this:

select t1.USER_ID, TIMEDIFF(t2.timestamp, t1.timestamp)
  from MOBILE_LOG  t1, MOBILE_LOG  t2
 where (t1.action,t1.timestamp) in (select action, max(timestamp) from MOBILE_LOG t where t.ACTION = "START_APP" group by USER_ID)
   and (t1.action,t1.timestamp) in (select action, max(timestamp), max(id) from MOBILE_LOG t where t.ACTION = "CLOSE_APP" group by USER_ID)
   and t1.USER_ID = t2.USER_ID

It will show you difference between two latest dates (startdate,enddate) for all user.

P.S: Sorry, I wrote it without any databases, and may be there are some mistakes. If you have problems with (t1.action,t1.timestamp) in (select...) split it on two: where t1.action in (select ...) and t1.timestamp in (select ...)

Upvotes: 2

valex
valex

Reputation: 24144

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

Upvotes: 5

Ariel
Ariel

Reputation: 26783

SELECT user_id, start_time, close_time, DATEDIFF(close_time, start_time) duration
FROM
   (SELECT MIN(timestamp) start_time, user_id FROM MOBILE_LOG WHERE action="START_APP" GROUP BY user_id) start_action
  JOIN
   (SELECT MAX(timestamp) close_time, user_id FROM MOBILE_LOG WHERE ACTION ="CLOSE_APP" GROUP BY user_id) close_action
  USING (user_id)
WHERE USER_ID="Alice";

You make two "tables" with the earliest time for start for each user, and the latest time for close for each user. Then join them so that the actions of the same user are together.

Now that you have everything setup you can easily subtract between them.

Upvotes: 3

Related Questions