无法对时间值求和，我不明白为什么？

Question

尽管有值，但我无法获得其中一个用户的值的总和时间。

尽管用户17有时间工作值，但我在sumTW列中得到该用户的NULL值。我该怎么解决？

我的查询是这样的：

SELECT DISTINCT Hours.*, CASE WHEN SUM(timeWorked) is null then '00:00' ELSE TIME_FORMAT(SUM(timeWorked), '%H:%i') END as sumTW FROM Hours 
WHERE Hours.stampingStatus = 1 GROUP BY Hours.whoWorked

这是表格代码：

-- phpMyAdmin SQL Dump
-- version 4.9.5
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: Apr 25, 2021 at 01:47 PM
-- Server version: 10.5.9-MariaDB
-- PHP Version: 7.4.16
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `stud_v20_keser`
--
-- --------------------------------------------------------
--
-- Table structure for table `Hours`
--
CREATE TABLE `Hours` (
`hourID` int(11) NOT NULL,
`taskID` int(11) DEFAULT NULL,
`whoWorked` int(11) NOT NULL,
`startTime` timestamp NOT NULL DEFAULT current_timestamp(),
`endTime` timestamp NOT NULL DEFAULT current_timestamp(),
`timeWorked` time NOT NULL DEFAULT '00:00:00',
`activated` tinyint(1) NOT NULL DEFAULT 1,
`location` varchar(30) COLLATE utf8_danish_ci DEFAULT NULL,
`phaseID` int(11) DEFAULT NULL,
`absenceType` varchar(30) COLLATE utf8_danish_ci DEFAULT NULL,
`overtimeType` int(1) DEFAULT NULL,
`comment` longtext COLLATE utf8_danish_ci DEFAULT NULL,
`commentBoss` longtext COLLATE utf8_danish_ci DEFAULT NULL,
`isChanged` tinyint(1) NOT NULL DEFAULT 0,
`stampingStatus` tinyint(1) NOT NULL DEFAULT 0,
`taskType` varchar(30) COLLATE utf8_danish_ci NOT NULL DEFAULT 'Definert oppgave'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_danish_ci;
--
-- Dumping data for table `Hours`
--
INSERT INTO `Hours` (`hourID`, `taskID`, `whoWorked`, `startTime`, `endTime`, `timeWorked`, `activated`, `location`, `phaseID`, `absenceType`, `overtimeType`, `comment`, `commentBoss`, `isChanged`, `stampingStatus`, `taskType`) VALUES
(295, 24, 1, '2021-04-22 12:02:46', '2021-04-22 16:02:50', '04:00:04', 1, 'sdf', NULL, NULL, NULL, 'Veldig brarn', 'Flott', 1, 1, 'Prosjekt'),
(299, 33, 3, '2021-04-24 14:43:01', '2021-04-24 14:43:10', '00:00:09', 1, 'Andre Verdenskrig', NULL, NULL, NULL, NULL, NULL, 0, 1, 'Prosjekt'),
(298, 24, 4, '2021-04-22 11:00:00', '2021-04-23 16:00:00', '29:00:00', 1, 'kj', NULL, NULL, NULL, NULL, NULL, 1, 1, 'Prosjekt'),
(300, NULL, 16, '2021-04-24 18:08:58', '2021-04-24 20:09:03', '02:00:05', 1, 'Hjemme', NULL, NULL, NULL, 'asdadsasdasda', NULL, 0, 1, 'Administrativt'),
(301, 35, 16, '2021-04-24 18:09:27', '2021-04-24 21:09:36', '03:00:09', 1, 'Mac', NULL, NULL, NULL, NULL, NULL, 0, 1, 'Prosjekt'),
(302, NULL, 17, '2021-04-24 18:16:31', '2021-04-24 20:18:03', '02:01:32', 1, 'New New York', NULL, NULL, NULL, NULL, NULL, 0, 1, 'Prosjekt'),
(303, NULL, 17, '2021-04-24 18:18:18', '2021-04-24 20:21:59', '02:03:41', 1, 'New New York', NULL, NULL, NULL, NULL, NULL, 0, 1, 'Prosjekt');
--
-- Triggers `Hours`
--
DELIMITER $$
CREATE TRIGGER `calculateTimeWorked` BEFORE UPDATE ON `Hours` FOR EACH ROW SET NEW.timeWorked = TIMEDIFF(new.endTime, new.startTime)
$$
DELIMITER ;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `Hours`
--
ALTER TABLE `Hours`
ADD PRIMARY KEY (`hourID`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `Hours`
--
ALTER TABLE `Hours`
MODIFY `hourID` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=304;
--
-- Constraints for dumped tables
--

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

(表格与原件不一样，原件有外键。我删除了它们只是为了能够重现同样的问题，同样的问题被重新产生(。

Answer 1

当尝试添加02:01:32和02:03:41时，MySQL(可能还有MariaDB(会发出警告：

mysql> select time_format(sum(t),'%H:%i:%s') from (select convert('02:01:32', time) as t union all select convert('02:03:41', time) ) x;
+--------------------------------+
| time_format(sum(t),'%H:%i:%s') |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '40473' |
+---------+------+-----------------------------------------+
1 row in set (0.00 sec)

求和时间的值为40473，因为这些时间加起来是4小时4分73秒。但是40473不能转换为时间，因为它是一种无用的格式。

另一种添加时间字段的方法：

select sec_to_time(sum(t)) 
from (select time_to_sec('02:01:32') as t 
union all 
select time_to_sec('02:03:41')) x;

输出：04:05:13

编辑：使用时间计算

select t.t, 0+t.t 
from (select cast(now() as time) as t) t;

这将输出：

t	0+t.t
14:12:11	141211