Mysql time format with auto %H:%i:%s

Question

This is my mysql query to select time from seconds.

SELECT TIME_FORMAT(SEC_TO_TIME(120),'%H:%i' )

This will return as 00:02 (HOUR AND MINUTES)

I need to show time hour,minutes,seconds automatically if there is a hour and there is a minute. no need to show like 00:00:10.

Is there any mysql function for doing this?

score 1 · Accepted Answer · answered Mar 20 '13 at 09:00

Table ss:

Query:

SQLFIDDLEExample

SELECT 
CASE WHEN id <60
      THEN TIME_FORMAT(SEC_TO_TIME(id),'%s' )
     WHEN id >= 60 AND id < 3600
      THEN TIME_FORMAT(SEC_TO_TIME(id),'%i:%s' )
     ELSE TIME_FORMAT(SEC_TO_TIME(id),'%H:%i:%s' ) END Time
 FROM ss

Result:

|     TIME |
------------
|       50 |
|    01:10 |
| 01:01:40 |

score 0 · Answer 2 · answered Mar 20 '13 at 06:15

0

You can try this

SELECT sec_to_time(now())

This function automatically gives you time in H:M:S

answered Mar 20 '13 at 06:15

Sharad

3,562
6
37
59

I tired with SELECT sec_to_time(360) but this also return 00:06:00 no need the 00 hour – App tester Mar 20 '13 at 06:23
See if this gives you only 06 then you will not know whether this is in hours , minute or in second. – Sharad Mar 20 '13 at 06:26

Mysql time format with auto %H:%i:%s

2 Answers2