Surpassing MySQL's TIME value limit of 838:59:59
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.";
?>
Have a look at timestampdiff which doesn't have the TIME limitation. I.e. something like (untested):
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
The concats not ideal, I'm sure there will be a more elegant solution.