php - 计算单个/每天每个用户的总时间 - 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

问题:

如何计算每个用户每天(“开始”、“停止”)的总时间？请参阅上面突出显示的点。假设，如果用户今天“开始”明天停止，那么今天的时间和明天的时间不一样？

现在我正在使用以下查询:-

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' )

从上面的查询中我得到了每天的记录，但是开始日期和停止日期是不同的。它计算的总时间不是一天的时间，而是我每天需要的时间。

Answer 1

我想我终于到了那里。你首先需要得到一组天数，我通过一个子查询获得它，该子查询采用开始和停止时间的 UNION (如果需要，你可以为你的 pinnumber 过滤它为了减小 JOIN 的大小)。

然后将每个这样的日期与包含该日期的那些 (start,stop) 对连接起来(即，要么在白天开始，要么一天的开始在开始和停止时间之间) .

最后，一个按天分组，取开始和结束时间之间的时间总和，在一天开始和结束时适当切断(神奇的 86400 是一天中的秒数 = 24*60*60)。遗憾的是，这在夏令时、闰秒等方面效果不佳……

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;

查看 sqlfiddle .