计算每个用户每天/每天的总时间 - php / mysql

Question

     > Start                    stop                  pinnumber
     > ---------------------------------------------------------
     > 2012-03-14 13:22:17    2012-03-14 15:22:50      2001
     > 2012-03-14 18:11:10    2012-03-14 19:10:10      2001
     > 2012-03-15 07:20:10    2012-03-15 13:20:50      2001
     >**2012-03-16 19:21:55       2012-03-17 02:55:22  2001** //on 16(19:21:55
                                                                     to 23:59:59) and     
                                                                 //on 17(00 to 02:55:22) 
     > 2012-03-17 14:15:05    2012-03-17 17:44:50      2001
     > 2012-03-18 19:11:10    2012-03-18 19:10:10      2002
     > 2012-03-18 10:20:10    2012-03-18 13:20:50      2003
     > 2012-03-18 11:20:10    2012-03-18 15:11:50      2001

Question:

How can I calculate total time of each user of per day ('start', 'stop') per day? Please see the above highlighted point. Suppose, If user 'start' today and stop it tomorrow then today hour are different and tomorrow hour are different?

right now i am using following query:-

SELECT SEC_TO_TIME( SUM( TIME_TO_SEC(TIMEDIFF(stop ,start ) ) ) ) AS time1, clock. * FROM table_name WHERE pin_number = '2001' GROUP BY DATE_FORMAT( start , '%W %M %Y' )

from above query i am getting per day records but when start date and stop date is different. it calculate total time not single day time but i need per day time.

Answer 1

I think I finally got there. You first need to get a set of days, which I obtain through a subquery that takes a UNION of the start and stop times (you could filter this for your pinnumber if desired in order to reduce the size of the JOIN).

One then joins each such date with those (start,stop) pairs that encompass that date (i.e. either start during the day, or the start of the day is between the start and stop time).

Finally, one groups by day and takes the sum of the amount of time between the start and end times, cutting off at the day start and end as appropriate (the magic 86400 is the number of seconds in a day = 24*60*60). Sadly this won't play nice with daylight savings, leap seconds, etc...

SELECT FROM_UNIXTIME(unixday, '%d/%m/%Y'), SUM(
    LEAST(   unixday+86400, UNIX_TIMESTAMP(Stop ))
  - GREATEST(unixday      , UNIX_TIMESTAMP(Start))
) AS Seconds
FROM table_name JOIN (

  SELECT UNIX_TIMESTAMP(DATE(Start)) AS unixday FROM table_name
UNION 
  SELECT UNIX_TIMESTAMP(DATE(Stop )) AS unixday FROM table_name

) AS days ON (
      UNIX_TIMESTAMP(Start) BETWEEN unixday AND unixday+86400
  OR (unixday BETWEEN UNIX_TIMESTAMP(Start) AND UNIX_TIMESTAMP(Stop))
)
WHERE pinnumber = 2001
GROUP BY unixday;

See it on sqlfiddle.