mysql - "minutes work" by users. How to make this query? -

i have 1 table format:

id, user, action, date 1, user1, a1, 2013-03-20 10:00:01 2, user2, a1, 2013-03-20 10:00:03 3, user2, a1, 2013-03-20 10:00:12 4, user3, a1, 2013-03-20 10:00:20 5, user2, a1, 2013-03-20 10:00:24 .... ... 

and go on on minutes , hours (24x7) 30 differents users...

now need know "minutes work"... example:

1. if have 1 user on day, have 24h*60m = 1440 minutes work today.
2. if 2 users in day (24h) be: 2880 minutes work...

but... users dont work day (of course..) how can make query?

answers questions:

• i need minutes work users (sum of minutes user)
• i asume "logout" of user if dont make action in 5 minutes.
• i can use php code make work.

this query it.

select user, cast(date date) day, (sum(worked_seconds) / 60) minutes (       select    w1.user       ,         w1.date       ,         unix_timestamp(                   least(                     min(ifnull(w2.date, w1.date + interval 5 minute)),                     cast(w1.date date) + interval 1 day                   )                 )                 - unix_timestamp(w1.date) worked_seconds             work w1       left join work w2       on        w1.user = w2.user       ,       w1.date < w2.date       ,       w1.date + interval 5 minute > w2.date       ,       cast(w1.date date) = cast(w2.date date)       group  w1.user, w1.date ) work group user, day 

the query pairs each work row later work rows of same user (on w1.user = w2.user) within 5 minute interval (and w1.date < w2.data , w1.date + interval 5 minute > w2.date), provided work occurred on same day (cast(w1.date date) = cast(w2.date date).

by definition, there no later row last row on particular day, , because we're using left join result in null value. assume last row, 5 minutes of work done per logout rule (ifnull(w2.date, w1.date + interval 5 minute)

it possible each (date, user) row multiple later rows found, in case want find earliest 1 can calcualte difference between 2 dates. done using min function.

now, since last row + 5 minute interval extend beyond end of day, use least prevent that.

the unix_timestamp's there convert datetimes seconds, can substract them , number of worked seconds.

in outer query, sum work per user , divide 60 in order seconds minutes.