CODEWITHSUNDEEP
mysqlsqlformat
user2899728
user2899728

Reputation: 2319

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

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?

Upvotes: 7

Views: 13177

Answers (4)

Parseeker
Parseeker

Reputation: 1

select DATE_FORMAT(SEC_TO_TIME(duration), '%i:%s') from messages;

Caveat: if duration > 1hr, it displays mm modulo 60. E.g. 4265 minutes will display as 11:05 and not 71:05.

Upvotes: 0

Eaten by a Grue
Eaten by a Grue

Reputation: 22931

If you are using MySQL 8.0+ you can use REGEXP_REPLACE like this to achieve a variable length string similar mickmackusa's answer:

REGEXP_REPLACE(SEC_TO_TIME(duration), '^(0|:)+', '')

Upvotes: 1

mickmackusa
mickmackusa

Reputation: 47873

I recently had a similar project where I needed to convert stored seconds to m:ss format. No matter the amount, there needed to be at least one digit representing minutes and two digits representing seconds. The hours placeholder was forbidden, so the minutes value could acceptably go beyond 59 and also contain more than 2 digits. The minute value must not be zero-padded.

This is what I used: (SQLFiddle Demo)

CONCAT(FLOOR(seconds/60), ':', LPAD(MOD(seconds,60), 2, 0)) AS `m:ss`

aka

CONCAT(FLOOR(seconds/60), ':', LPAD(seconds%60, 2, 0)) AS `m:ss`

seconds  |   m:ss
-----------------
      0  |   0:00
      1  |   0:01
     10  |   0:10
     60  |   1:00
     61  |   1:01
     71  |   1:11
   3599  |  59:59
   3600  |  60:00
   5999  |  99:59
   6000  | 100:00

TIME_FORMAT(SEC_TO_TIME(seconds),'%i:%s') was unsuitable because the project specifications did not want the minute portion to be zero-padded. Here is a good post relating to this technique.

There is no single-digit minute option in TIME_FORMAT() or DATE_FORMAT().

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269493

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)

Upvotes: 8

Related Questions