Question 21624 in Mysql Question posted in
The official MySQL documentation can be found here.

convert hours to hh:mm in mysql

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
email working_hours
[email protected] 5.77
[email protected] 4.24
[email protected] 2.69
[email protected] 6.12

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
email working_hours
[email protected] 00:05:46.20
[email protected] 00:04:14.40
[email protected] 00:02:41.40
[email protected] 00:06:07.20

Expected output would be like

email working_hours
[email protected] 05:46
[email protected] 04:14
[email protected] 02:41
[email protected] 06:07
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    +all thanks for the help. I got answer to my question,

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


  2. 0 
    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;
    email total_time truncated rounded formatted_truncated
    [email protected] 05:46:12.00 05:46 05:46 05:46
    [email protected] 04:14:24.00 04:14 04:14 04:14
    [email protected] 02:41:24.00 02:41 02:41 02:41
    [email protected] 06:07:48.00 06:07 06:08 06:07

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search