MySQL 5.7

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE IF NOT EXISTS `clockintest` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `PartnerId` bigint(20) unsigned DEFAULT NULL,
  `TimeStamp` datetime DEFAULT NULL,
  `City` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Street` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Postalcode` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Country` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CommissionId` bigint(20) unsigned DEFAULT NULL,
  `WorkDayId` bigint(20) unsigned DEFAULT NULL,
  `UserId` bigint(20) unsigned DEFAULT NULL,
  `WorkDescription` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `EmployeeId` bigint(20) unsigned DEFAULT NULL,
  `Type` enum('Start','End') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `Kostenstelle` int(11) DEFAULT NULL,
  `Sachkonto` int(11) DEFAULT NULL,
  `Bearbeitungsschluessel` varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `CreatedAt` datetime DEFAULT current_timestamp(),
  `UpdatedAt` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `DeletedAt` datetime DEFAULT NULL,
  `ExportedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `Id` (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=712 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Dumping data for table basedb.clockintest: ~6 rows (approximately)
DELETE FROM `clockintest`;
/*!40000 ALTER TABLE `clockintest` DISABLE KEYS */;
INSERT INTO `clockintest` (`Id`, `PartnerId`, `TimeStamp`, `City`, `Street`, `Postalcode`, `Country`, `CommissionId`, `WorkDayId`, `UserId`, `WorkDescription`, `EmployeeId`, `Type`, `Kostenstelle`, `Sachkonto`, `Bearbeitungsschluessel`, `CreatedAt`, `UpdatedAt`, `DeletedAt`, `ExportedAt`) VALUES
    (645, 1, '2021-10-26 08:00:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'Start', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-27 11:43:20', NULL, NULL),
    (649, 1, '2021-10-26 08:00:00', NULL, NULL, NULL, NULL, NULL, 150, 1, NULL, 1, 'Start', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-29 13:02:50', NULL, NULL),
    (680, 1, '2021-10-26 10:00:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'End', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-28 08:46:05', NULL, NULL),
    (684, 1, '2021-10-26 12:00:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'Start', NULL, NULL, NULL, '2021-10-27 11:41:29', '2021-10-29 07:37:51', NULL, NULL),
    (709, 1, '2021-10-26 23:59:00', NULL, NULL, NULL, NULL, NULL, 149, 1, NULL, 1, 'End', NULL, NULL, NULL, '2021-10-29 09:52:07', '2021-10-29 09:52:07', NULL, NULL),
    (710, 1, '2021-10-26 23:59:00', NULL, NULL, NULL, NULL, NULL, 150, 1, NULL, 1, 'End', NULL, NULL, NULL, '2021-10-29 09:52:07', '2021-10-29 13:02:44', NULL, NULL);
CREATE TABLE workday (id int, TimeSpan bigint, BreakTime BIGINT )
INSERT INTO workday VALUES (149,0,0)
SELECT c.Id, c.TimeStamp, c.WorkDayId, c.EmployeeId, c.`Type` 
                       FROM clockintest c WHERE c.WorkDayId = 149 ORDER BY c.TimeStamp;


Id
TimeStamp
WorkDayId
EmployeeId
Type


645
2021-10-26 08:00:00
149
1
Start

680
2021-10-26 10:00:00
149
1
End

684
2021-10-26 12:00:00
149
1
Start

709
2021-10-26 23:59:00
149
1
End


DROP PROCEDURE IF EXISTS calculation ;

CREATE PROCEDURE calculation (IN workDayId INT)
BEGIN 
    DECLARE TimeSpan INT DEFAULT 0;
    DECLARE BreakTime INT DEFAULT 0;
    DECLARE c_Id INT DEFAULT 0;
    DECLARE c_TimeStamp DateTime;
    DECLARE c_WorkDayId INT DEFAULT 0;
    DECLARE c_EmployeeId INT DEFAULT 0;
    DECLARE c_Type VARCHAR(10);
    DECLARE n_TimeStamp DateTime;
    DECLARE n_Id INT DEFAULT 0;
    DECLARE n_WorkDayId INT DEFAULT 0;
    DECLARE n_EmployeeId INT DEFAULT 0;
    DECLARE n_Type VARCHAR(10);
    DECLARE cur_Type VARCHAR(100) DEFAULT "";
    DECLARE done INT DEFAULT FALSE;
    DECLARE cursor_clockIn CURSOR FOR SELECT c.Id, c.TimeStamp, c.WorkDayId, c.EmployeeId, c.`Type` 
                       FROM clockintest c WHERE c.WorkDayId = workDayId ORDER BY c.TimeStamp;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cursor_clockIn;
    SET TimeSpan = 0;
    SET BreakTime = 0;
        FETCH cursor_clockIn INTO c_Id, c_TimeStamp, c_WorkDayId, c_EmployeeId, c_Type;
        SET @a = c_Id;  
    loop_through_rows: LOOP

        BEGIN
          
        FETCH NEXT FROM cursor_clockIn INTO n_Id, n_TimeStamp, n_WorkDayId, n_EmployeeId, n_Type;

        IF done THEN 
            LEAVE loop_through_rows;
        END IF;
      SET @a = CONCAT(@a,';', n_id);      
        IF c_Type = 'Start' THEN
            SET TimeSpan = TimeSpan + TIMESTAMPDIFF(MICROSECOND, c_TimeStamp, n_TimeStamp) / 1000;
        END IF;
        
        IF c_Type = 'End' THEN
            SET BreakTime = BreakTime + TIMESTAMPDIFF(MICROSECOND, c_TimeStamp, n_TimeStamp) / 1000;
        END IF;

        
            SET cur_Type = CONCAT(cur_Type,';', c_Id );
            SET c_Id = n_id;
            SET c_TimeStamp = n_TimeStamp;
            SET c_WorkDayId = n_WorkDayId;
            SET c_EmployeeId = n_EmployeeId;
            SET c_Type = n_Type;
            END;
    END LOOP;
    SELECT TimeSpan, BreakTime, cur_Type;
        UPDATE workday w SET w.TimeSpan = TimeSpan, w.BreakTime = BreakTime WHERE w.Id = workDayId;

    CLOSE cursor_clockIn;

END 
CALL calculation (149);


TimeSpan
BreakTime
cur_Type


50340000
7200000
;645;680;684


SELECT @a


@a


645;680;684;709


SELECT * FROM workday


id
TimeSpan
BreakTime


149
50340000
7200000
Id	TimeStamp	WorkDayId	EmployeeId	Type
645	2021-10-26 08:00:00	149	1	Start
680	2021-10-26 10:00:00	149	1	End
684	2021-10-26 12:00:00	149	1	Start
709	2021-10-26 23:59:00	149	1	End