超过 MySQL 838:59:59 的 TIME 值限制

2022-01-09 00:00:00 time sum mysql

标题可能有点混乱，所以请允许我解释一下.我正在使用表格来记录我的工作日志.每天我都会创建一个条目，说明我从什么时间工作到什么时间，我会添加一条评论来描述我所做的事情.

The title might be a bit confusing so allow me to explain. I'm using a table to record my work logs. Every day I'll create an entry stating from what time to what time I have worked and I'll add a comment describing what I did.

然后我使用查询来比较时间戳，以确定我当天工作了多少小时和分钟.此外，我使用查询来计算我全年工作的小时数和分钟数的总和.那就是我遇到问题的地方.我的查询如下.

I then use a query to compare the timestamps to figure out exactly how many hours and minutes I have worked that day. Additionally, I use a query to calculate the sum of hours and minutes I have worked the entire year. That's where I'm running into a problem. My query is as follows.

SELECT TIME_FORMAT(SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(entry_end_time, entry_start_time)))), '%H:%i') 
AS total FROM entry 
WHERE entry_date BETWEEN '2012-01-01' AND '2012-12-31' AND user_id = 3

默认情况下，MySQL TIME 字段允许的时间范围为-838:59:59"到838:59:59".不过，今年我目前已经记录了 900 多个小时的工作，我希望我的查询结果能够反映这一点.相反，结果是 838:59:59，这是有道理的，因为这是限制.

By default, MySQL TIME fields allow a time range of '-838:59:59' to '838:59:59'. I have currently logged more than 900 hours of work this year though, and I want the result of my query to reflect this. Instead, the result is 838:59:59, which makes sense because that is the limit.

有什么办法可以让查询的结果超过 839 小时，还是我必须使用 PHP 之类的东西来遍历整个表并将其全部加起来?如果可能的话，我有点想避免这种情况.

Is there any way around this so the result of the query can go beyond 839 hours, or would I have to use something like PHP to go over the entire table and add it all up? I kind of want to avoid that if possible.

推荐答案

我只是检索工作的总秒数，并在我的应用程序的表示层中根据需要转换为小时/分钟(毕竟, 除以 60 的简单例子):

I'd just retrieve the total number of seconds worked, and convert to hours/minutes as required in the presentation layer of my application (it is, after all, a simple case of division by 60):

<?
  $dbh = new PDO("mysql:dbname=$dbname", $username, $password);
  $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);

  $qry = $dbh->prepare('
    SELECT SUM(TIME_TO_SEC(entry_end_time)-TIME_TO_SEC(entry_start_time))
    FROM   entry 
    WHERE  entry_date BETWEEN :start_date AND :end_date
       AND user_id = :user_id
  ');

  $qry->execute([
    ':start_date' => '2012-01-01',
    ':end_date'   => '2012-12-31',
    ':user_id'    => 3
  ]);

  list ($totalMins, $remngSecs) = gmp_div_qr($qry->fetchColumn(), 60);
  list ($totalHour, $remngMins) = gmp_div_qr($totalMins, 60);

  echo "Worked a total of $totalHour:$remngMins:$remngSecs.";
?>

相关文章