In MySQL, you can use the TIME_FORMAT() function to format a time value.

This function works just like the DATE_FORMAT() function, except that the value can only be formatted in hours, minutes, seconds, and microseconds.

See the table at the bottom of this article for a list of format specifiers that can be used with this function.

Syntax

The syntax goes like this:

TIME_FORMAT(time,format)

Where time is the time value you want formatted, and format is the format string (this determines how the time value is actually formatted).

Example 1 – Basic Usage

Here’s an example to demonstrate.

SELECT TIME_FORMAT('14:35:27', '%r') AS 'Result';

Result:

+-------------+
| Result      |
+-------------+
| 02:35:27 PM |
+-------------+

This example uses the %r format specifier, which formats the time as 12-hour (hh:mm:ss followed by AM or PM).

Example 2 – More Specific Formatting

If using a more general format specifier like in the previous example isn’t suitable, there are various other format specifiers that can help you build the format you require.

For example, if you like the previous format, but you don’t want the seconds to be returned, you could do this:

SELECT TIME_FORMAT('14:35:27', '%h:%i %p') AS 'Result';

Result:

+----------+
| Result   |
+----------+
| 02:35 PM |
+----------+

Example 3 – 24 Hour Time

There are also format specifiers for 24 hour time. Here’s the easiest way to format the time using 24 hour time.

SELECT TIME_FORMAT('14:35:27', '%T') AS 'Result';

Result:

+----------+
| Result   |
+----------+
| 14:35:27 |
+----------+

Example 4 – Microseconds

Here’s an example that includes the fractional seconds part in the result:

SELECT TIME_FORMAT('14:35:27', '%H:%i:%s.%f') AS 'Result';

Result:

+-----------------+
| Result          |
+-----------------+
| 14:35:27.000000 |
+-----------------+

Of course, we can also include microseconds in the initial time value:

SELECT TIME_FORMAT('14:35:27.123456', '%H:%i:%s.%f') AS 'Result';

Result:

+-----------------+
| Result          |
+-----------------+
| 14:35:27.123456 |
+-----------------+

Example 5 – Elapsed Time

The time data type is not limited to 24 hour time. It can also be used to represent elapsed time. When using this function for elapsed time, you should be aware of how the hour format specifiers work with time values outside the range 0 to 23.

The MySQL documentation states the following:

If the time value contains an hour part that is greater than 23, the %H and %k hour format specifiers produce a value larger than the usual range of 0..23. The other hour format specifiers produce the hour value modulo 12.

Here’s an example to demonstrate:

SELECT 
    TIME_FORMAT('24:00:00', '%H %k %h %I %l') AS '24',
    TIME_FORMAT('48:00:00', '%H %k %h %I %l') AS '48',
    TIME_FORMAT('100:00:00', '%H %k %h %I %l') AS '100',
    TIME_FORMAT('500:00:00', '%H %k %h %I %l') AS '500';

Result:

+----------------+----------------+-----------------+-----------------+
| 24             | 48             | 100             | 500             |
+----------------+----------------+-----------------+-----------------+
| 24 24 12 12 12 | 48 48 12 12 12 | 100 100 04 04 4 | 500 500 08 08 8 |
+----------------+----------------+-----------------+-----------------+

Format Specifiers

The following specifiers can be used to specify the return format. These are a subset of those available with the DATE_FORMAT() function. The format value must start with a percentage sign (%).

Specifier	Description
%f	Microseconds (000000..999999)
%H	Hour (00..23)
%h	Hour (01..12)
%I	Hour (01..12)
%i	Minutes, numeric (00..59)
%k	Hour (0..23)
%l	Hour (1..12)
%p	AM or PM
%r	Time, 12-hour (hh:mm:ss followed by AM or PM)
%S	Seconds (00..59)
%s	Seconds (00..59)
%T	Time, 24-hour (hh:mm:ss)
%%	A literal % character