i have data which is below has email and working hours (in hours formate), expecting output of working_hours to hh:mm:ss

select d.email,TRUNCATE(sum(e.hours),2) as "working_hours"
from users d inner join proj_time e on d.id=e.user_id
WHERE start_time BETWEEN CURDATE() - INTERVAL 1 DAY
AND CURDATE() - INTERVAL 1 SECOND
group by d.id,d.email

i tried below query to change working_hours, BUT getting leading zeros, requested output to remove leading zeros (00:) from working_hours output

select d.email,SEC_TO_TIME(TRUNCATE(sum(e.hours * 60),2)) as "working_hours"
from users d inner join proj_time e on d.id=e.user_id
WHERE start_time BETWEEN CURDATE() - INTERVAL 1 DAY
AND CURDATE() - INTERVAL 1 SECOND
group by d.id,d.email

Expected output would be like

CodePudding user response：

all thanks for the help. i got answer for my question,

SEC_TO_TIME(ROUND(TRUNCATE(sum(e.hours * 3600),2)))

CodePudding user response：

CREATE TABLE test (email VARCHAR(255), working_hours DECIMAL(10,2));
INSERT INTO test VALUES
('[email protected]',     5.77),
('[email protected]',   4.24),
('[email protected]',    2.69),
('[email protected]',    6.13);
SELECT email,
       SEC_TO_TIME(SUM(working_hours * 3600)) total_time,
       SUBSTRING_INDEX(SEC_TO_TIME(SUM(working_hours * 3600)), ':', 2) truncated,
       SUBSTRING_INDEX(SEC_TO_TIME(SUM(working_hours * 3600   30)), ':', 2) rounded,
       DATE_FORMAT(SEC_TO_TIME(SUM(working_hours * 3600)), '%h:%i') formatted_truncated
FROM test
GROUP BY 1;

fiddle