CODEWITHSUNDEEP
mysql
mohanj.2010
mohanj.2010

Reputation: 1

How to convert seconds into "DD:HH:MM" format

I tried to use SEC_TO_TIME(seconds) and worked and gave result as below

SEC_TO_TIME(161295.38237498334)

result:

44:48:15.382375

I need to get result in (DD:HH:MM)

Upvotes: 0

Views: 2430

Answers (1)

ttton
ttton

Reputation: 616

If you're using UNIX timestamps as @Mark Leiber mentions, then take a look at FROM_UNIXTIME() and DATE_FORMAT().

If you are NOT using timestamps, but instead want to format an arbitrary time value, then you could look at the TIME_FORMAT() function:

SELECT TIME_FORMAT(SEC_TO_TIME(161295.38237498334), '%H:%i:%s')

Unfortunately TIME_FORMAT can only represent the hours and minutes portion. To get the number of days you could use:

FLOOR(HOUR(SEC_TO_TIME(161295.38237498334)) / 24)

then format the remaining hours and minutes:

TIME_FORMAT(SEC_TO_TIME(161295.38237498334 % (24 * 60 * 60)), '%H:%i'))

You can then put it altogether using CONCAT(). See this fiddle for a complete working example.

You can refer to the whole list of MySQL time and date functions here: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html

See also @RichardTheKiwi's answer to this related question.

Upvotes: 3

Related Questions