Delete values ​​00 from TIME_FORMAT ()

Question

From this request

SELECT TIME_FORMAT("19:00:00", "%H Hours, %i Minutes, %s Seconds");

I get the result 19 Hours, 00 Minutes, 00 Seconds

What I want to get is 19 HoursOnly if Minutes and Seconds = 00.

If I have 19:00:55, I expect to receive19 Hours, 55 Seconds

And if I have 19:55:00, I expect to receive19 Hours, 55 Minutes

deleting values 00using SQL

Answer 1

I tested this:

SET @t = '19:00:00';

SELECT CONCAT_WS(', ', 
  CONCAT(NULLIF(TIME_FORMAT(@t, '%H'), '00'), ' hours'), 
  CONCAT(NULLIF(TIME_FORMAT(@t, '%i'), '00'), ' minutes'), 
  CONCAT(NULLIF(TIME_FORMAT(@t, '%s'), '00'), ' seconds')) AS time_expr;

Conclusion:

+-----------+
| time_expr |
+-----------+
| 19 hours  |
+-----------+

When I set the time to something else:

SET @t = '19:00:05';

Conclusion:

+----------------------+
| time_expr            |
+----------------------+
| 19 hours, 05 seconds |
+----------------------+

It even processes zero hours:

SET @t = '00:43:00';

Conclusion:

+------------+
| time_expr  |
+------------+
| 43 minutes |
+------------+

Answer 2

This is a small variation of Bill's answer, which handles plurals in temporary parts:

SELECT CONCAT_WS(', ', 
                 CONCAT(hour(t), ' hour', (case hour(t) when 0 then NULL when 1 then '' else 's' end)), 
                 CONCAT(minute(t), ' minute', (case minute(t) when 0 then NULL when 1 then '' else 's' end)), 
                 CONCAT(second(t), ' second', (case second(t) when 0 then NULL when 1 then '' else 's' end))
                ) AS time_expr
from (SELECT CAST('19:01:01' as time) as t) x

Answer 3

you can try this.

SELECT TIME_FORMAT( "19:00:00", 
    CONCAT(IF(HOUR("19:00:00") <> 0, "%H Hours", "") ,
    IF(MINUTE("19:00:00") <> 0, ", %i Minutes", "") ,
    IF(SECOND("19:00:00") <> 0, ", %s Seconds", ""))
);