CODEWITHSUNDEEP
mysqldatetimetime
chris
chris

Reputation: 371

MySQL function to format time in SQL query as mm:ss

MySQL NOOB having issues...

I have a column in my database called TotalTime which stores elapsed time (hh:mm:ss). In the table it is stored as type = time.

When it is displayed it shows hh:mm:ss. Thus, 3:55 displays as 00:03:55.

In my SQL query is there a way to format to show 03:55 (or better yet 3:55)?

Beginning of query looks like:

SELECT
    OverallRank,
    First,
    Last,
    TotalTime,
    AgeGroup...

Many thanks.

Upvotes: 1

Views: 968

Answers (4)

swapnesh
swapnesh

Reputation: 26732

Use 

i for minutes
s for seconds

like-- i:s

%i  Minutes, numeric (00..59)
%s  Seconds (00..59)

EDIT 

SELECT OverallRank, First, Last, DATE_FORMAT(TotalTime, "%i:%s") as TotalTime,...
FROM tbl_name

Upvotes: 2

user3266190
user3266190

Reputation:

You can prehaps use DATE_FORMAT()? http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

SELECT OverallRank, First, Last, DATE_FORMAT(TotalTime, "%i:%s") as TotalTime, AgeGroup..

Upvotes: 2

Nelson Benítez León
Nelson Benítez León

Reputation: 50563

If you want to obtain your 3:55 output rather than 03:55 you can also use an IF construct:

SELECT OverallRank, 
       First, 
       Last, 
       IF (LEFT(DATE_FORMAT(TotalTime, "%i:%s"),1) == '0',     #IF
                SUBSTRING(DATE_FORMAT(TotalTime, "%i:%s"),2) , #yes part
                DATE_FORMAT(TotalTime, "%i:%s")) as TotalTime, #no part
       AgeGroup..

Upvotes: 0

CristiC
CristiC

Reputation: 22698

Check the date_format function.

You can do something like:

SELECT
    OverallRank,
    First,
    Last,
    DATE_FORMAT(TotalTime, '%i:%s'),
    AgeGroup...

Upvotes: 0

Related Questions