Remove 00 values from TIME_FORMAT()

Question

From this query

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 Hours Only if Minutes and Seconds = 00.

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

And If i have 19:55:00 I expect to get 19 Hours, 55 Minutes

by removing the 00 values using 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;

Output:

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

When I set time to something else:

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

Output:

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

It even handles zero hours:

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

Output:

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

Answer 2

This is a slight variation of Bill's answer, which handles plurals on the time 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