I've got a single MySQL table with login data of customers.


user   date                type

-----+---------------------+------

1     2011-01-05 08:00:00  login

1     2011-01-06 09:00:00  login

1     2011-01-06 10:00:00  logout

1     2011-01-06 09:50:00  login

Because of the above table I must calculate time distinction between the logout date and also the previous login date with the addition of a brand new cell known as duration. E.g. the logout date was '2011-01-06 10:00:00 and also the previous login date could be '2011-01-06 09:50:00'.

The end result need to look in some way such as this. Rows with type=login shouldn't possess a duration value.


user   date                type    duration

-----+---------------------+--------+----------

1     2011-01-05 08:00:00  login   -

1     2011-01-06 09:00:00  login   -

1     2011-01-06 10:00:00  logout  10min

1     2011-01-06 09:50:00  login   -

Thanks ahead of time,
mawo

SELECT x.*, TIMEDIFF(x.logout_date, x.login_date) as duration
FROM
(
SELECT a.user_id, a.`date` as logout_date, 
(SELECT MAX(b.`date`) FROM table1 b WHERE b.`date` <a.`date` 
and b.user=a.user and b.type = 'login') as login_date    
FROM table1 a WHERE a.type ='logout'
)x