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.
Answer 1:
我只是检索工作的总秒数，并在我的应用程序的表示层需要转换成小时/分钟（是的，毕竟，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.";
?>
Answer 2:
看看TIMESTAMPDIFF不有时间限制。 即像（未经测试）：
SELECT CONCAT(
TIMESTAMPDIFF(HOURS, entry_end_time, entry_start_time),
":",
MOD(TIMESTAMPDIFF(MINUTES, entry_end_time, entry_start_time),60)
)
AS total FROM entry
WHERE entry_date BETWEEN '2012-01-01' AND '2012-12-31' AND user_id = 3
不理想的concats，我相信会有一个更好的解决方案。
Answer 3:
分别计数的天就够了。
下面是我使用的级联。
我有一个完全复制/ pastable例子说明，以缓解我们打了极限的理解（ TIME格式的最大值）
免费麒麟捆绑简化逗号管理
SELECT 'pq7~' AS unicorn
#######################
## Expected result ##
#######################
## Total, formatted as days:hh:mm:ss ##
,CONCAT(
FLOOR(TIMESTAMPDIFF(SECOND, '2017-01-01 09:17:45', '2017-03-07 17:06:24') / 86400)
, ':'
, SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2017-01-01 09:17:45', '2017-03-07 17:06:24') % 86400)
) AS Real_expected_result
#########################
## Calculation details ##
#########################
## Extracted days from diff ##
,FLOOR(TIMESTAMPDIFF(SECOND, '2017-01-01 09:17:45', '2017-03-07 17:06:24') / 86400) AS Real_days
## Extracted Hours/minutes/seconds from diff ##
,SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2017-01-01 09:17:45', '2017-03-07 17:06:24') % 86400) AS Real_hours_minutes_seconds
###################################
## Demo of wrong values returned ##
###################################
,TIMESTAMPDIFF(SECOND, '2017-01-01 09:17:45', '2017-03-07 17:06:24') AS Real_seconds_diff
## WRONG value returned. 5.64M is truncated to 3.02 ! ##
,TIME_TO_SEC(SEC_TO_TIME(5644119)) AS WRONG_result
## Result is effectively limited to 838h59m59s ##
,SEC_TO_TIME(TIMESTAMPDIFF(SECOND, '2017-01-01 09:17:45', '2017-03-07 17:06:24')) AS Limit_hit
## Lights on said limit ##
,SEC_TO_TIME( 3020398) AS Limit_value_check1
,SEC_TO_TIME( 3020400) AS Limit_value_check2
Answer 4:
一些简单的数学可以做的伎俩，我硬编码的秒随机数（10000000）
SELECT CONCAT(FLOOR(10000000/3600),':',FLOOR((10000000%3600)/60),':',(10000000%3600)%60)
小提琴
2777:46:40
Answer 5:
select concat(truncate(sum(time_to_sec(TIMEDIFF(hora_fim, hora_ini)))/3600,0), ':',
TIME_FORMAT(sec_to_time(sum(time_to_sec(TIMEDIFF(hora_fim, hora_ini))) - truncate(sum(time_to_sec(TIMEDIFF(hora_fim, hora_ini)))/3600,0)*3600), '%i:%s'))
as hms from tb_XXXXXX
Answer 6:
首先计算天差然后乘以24 * 60 * 60，将其转换成秒钟，然后添加到它TIME_TO_SEC值结果
DATEDIFF(start_date,end_date)*24*60*60 + TIME_TO_SEC(TIMEDIFF(TIME(start_date),TIME(end_date)))
AS sec_diff
有关详情，请codebucket-超越TIME_TO_SEC（）函数的最大限制
文章来源: Surpassing MySQL's TIME value limit of 838:59:59
