mysql minutes to hours and minutes
18,613
Solution 1
You should look up FLOOR() function, and simple math. You need something like
CONCAT(FLOOR(minutes/60),'h ',MOD(minutes,60),'m')
Solution 2
CONCAT(FLOOR(minutes/60),':',LPAD(MOD(minutes,60),2,'0'))
a variation on Damir Kasipovic's code: this converts 62 minutes to '1:02'
Solution 3
Why not simply do something like this?
select convert(date_add('2000-01-01', interval 146 minute), time);
This returns 02:26:00
Or the following if you want to specify return format
select date_format(date_add('2000-01-01', interval 146 minute), '%h:%i');
Author by
Jefferson Rosa
Updated on August 05, 2022Comments
-
Jefferson Rosa almost 2 years
I have a table that return minutes from a task and I'd like to convert into hours and minutes.
my select is:
select m.mat_nome as 'Matéria', round( sum( case when Hour(TIMEDIFF(est_horario_inicial, est_horario_final))*60 = 0 then Minute(TIMEDIFF(est_horario_inicial, est_horario_final)) else Hour(TIMEDIFF(est_horario_inicial, est_horario_final))*60 end )/60 ,2) as 'tempo' from estudos_realizados e left join materias m on e.mat_id = m.mat_id;
so this is returning 100 minutes from my table and 1.67 when divided by 60. I'd like to get the result 1h40m The hours and minutes.
This is possible? How can I divide the number to 60 and mod the decimals to 60?
Thank you!
-
Zeeshan Eqbal about 4 yearsThanks for saving my time.