Cannot sum time values, I don't understand why?

Question

Despite values are there I can't get the sum time of values of one of the user.

Even despite user 17 has time worked values, I get NULL value for that user on sumTW column. How do I fix that?

This is how my query looks like:

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

Here's the table code:

-- 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 bra\r\n', '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 */;

(The table is not same as the original, the original has foreign keys. I removed them just to be able to reproduce same problem, the same problem is reporoduced).

Answer 1

When trying to add 02:01:32 and 02:03:41, MySQL (and probably MariaDB too) issues a warning:

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)

Summing time give a value of 40473, because those times sum to 4 hours 4 minutes and 73 seconds. But 40473 is not convertible to time, because it is a useless format.

Another way of adding time-fields:

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;

output: 04:05:13

EDIT: calculating with time

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

This will output:

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

The second column shows as time without :, but is a number which should not be used for calculations (which is done when using SUM() on a time-field).