MySQL: How to convert seconds to mm:ss format?

Question

I want to convert seconds to minute : seconds format in sql select statement.

At the moment I am using:

SELECT SEC_TO_TIME(duration) from messages; 

It works perfectly but it gives me this format of time: hh:mm:ss but I need mm:ss

Is it possible to convert seconds into mm:ss format using sql query?

Answer 1

If the value is less than an hour, then just do:

SELECT RIGHT(SEC_TO_TIME(duration), 5) from messages; 

If you might go over an hour, then do the arithmetic:

SELECT CONCAT_WS(':', FLOOR(SEC_TO_TIME(duration) / 60),
                 SEC_TO_TIME(duration) % 60)