mysqltimedecimal

mysql minutes to hours and minutes

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!

Solution

  • You should look up FLOOR() function, and simple math. You need something like

    CONCAT(FLOOR(minutes/60),'h ',MOD(minutes,60),'m')