CODEWITHSUNDEEP
mysqlsql
user964970
user964970

Reputation:

MySQL, present seconds in mm:ss format

Is there an easy way to convert seconds to an mm:ss format (minutes and seconds)? I'm doing e.g.

select colA,ROUND(AVG(seconds)) from table group by colA.

I'd like to show this in an mm:ss format (note that this is not what the MySQL SEC_TO_TIME does, it always gives you the HH part as well)

if seconds is 90, i'd like "1:30", or "01:30" would be ok too. If seconds is 3602 , either of "60:02" or "01:00:02" would be fine, but truncating off the hour to "00:02" would not be..

Upvotes: 0

Views: 417

Answers (2)

Andomar
Andomar

Reputation: 238078

You could use floor to find the hours, and mod to find the minutes:

select  concat(floor(column_with_seconds/60), 
        ':',
        mod(column_with_seconds,60))

Working example at SqlLize.

Upvotes: 0

ajreal
ajreal

Reputation: 47321

This is one possible way :-

mysql> set @sec:= 3602;
Query OK, 0 rows affected (0.00 sec)

mysql> select concat( lpad(floor(@sec/60), 2, 0), ':', lpad((@sec%60), 2, 0));
+-------------------------------------------------------------------+
| concat( lpad(floor(3602/60), 2, 0), ':', lpad((3602 % 60), 2, 0)) |
+-------------------------------------------------------------------+
| 60:02                                                             |
+-------------------------------------------------------------------+

Upvotes: 1

Related Questions