mysql time 838,超过MySQL的TIME值限制838：59：59

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

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.

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.

解决方案

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.";

?>