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

2021-12-23 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.";
?>

相关文章