Smart formatting of time span in MYSQL?-CodePudding

I have query which has a column which is a timespan in seconds and would like to display it with logic shown below, (not necessary for it to be exactly as shown). Are there any built in MYSQL formatting functions for timespans, or possibly "libraries" of functions? We could roll our own, but it seems like this should be a pretty standard need.

T < 60:
T sec

60 < T < 3600
T/60 min

3600 < T < (86400 * 2)
X hr Y min

(86400 * 2) < T
Z days

CodePudding user response：

You could use the build in function SEC_TO_TIME()
This only gives you 01:00:10 Hours:Minutes:Seconds

You could also try this:

SELECT CONCAT(
            FLOOR(TIME_FORMAT(SEC_TO_TIME([column]), '%H') / 24), 'days ',
            MOD(TIME_FORMAT(SEC_TO_TIME([column]), '%H'), 24), 'h:',
            TIME_FORMAT(SEC_TO_TIME([column]), '%im:%ss')
        )
AS Result FROM [table]

This gives you

11days 18h:05m:57s