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

Question

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)

possible duplicate of [MySQL date format DD/MM/YYYY select query?](http://stackoverflow.com/questions/10637581/mysql-date-format-dd-mm-yyyy-select-query) — Najera, May 18 '15 at 05:44
If you're using seconds since 1970 (Unix timestamps), see http://stackoverflow.com/questions/5385349/how-does-one-convert-seconds-or-milliseconds-to-a-timestamp-or-just-a-string — Mark Leiber, May 18 '15 at 05:45

score 3 · Answer 1 · edited May 23 '17 at 12:14

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.

1 Answers1