Below are seven functions we can use to extract the hours, minutes, seconds, and microseconds from a time value in MySQL.

The HOUR(), MINUTE(), SECOND() and MICROSECOND() Functions

The HOUR(), MINUTE(), SECOND(), and MICROSECOND() functions extract the hours, minutes, seconds, and microseconds from a given time value.

Here’s an example of using these functions to return each time component in its own column:

SET @time = '12:17:38.123456';
SELECT 
    HOUR( @time ) AS "Hours",
    MINUTE( @time ) AS "Minutes",
    SECOND( @time ) AS "Seconds",
    MICROSECOND( @time ) AS "Microseconds";

Result:

+-------+---------+---------+--------------+
| Hours | Minutes | Seconds | Microseconds |
+-------+---------+---------+--------------+
|    12 |      17 |      38 |       123456 |
+-------+---------+---------+--------------+

MySQL TIME values can be in the range '-838:59:59.000000' to '838:59:59.000000', so they can also be negative. When the value is negative, the return value doesn’t include the sign.

Here’s an example of providing a negative time value:

SET @time = '-838:59:59.000000';
SELECT 
    HOUR( @time ) AS "Hours",
    MINUTE( @time ) AS "Minutes",
    SECOND( @time ) AS "Seconds",
    MICROSECOND( @time ) AS "Microseconds";

Result:

+-------+---------+---------+--------------+
| Hours | Minutes | Seconds | Microseconds |
+-------+---------+---------+--------------+
|   838 |      59 |      59 |            0 |
+-------+---------+---------+--------------+

The EXTRACT() Function

The EXTRACT() function allows us to extract a specified unit from the date/time value. So this function provides us with another way to extract the hours, minutes, seconds, and microseconds from the time value:

SET @time = '12:17:38.123456';
SELECT 
    EXTRACT( HOUR FROM @time ) AS "Hours",
    EXTRACT( MINUTE FROM @time ) AS "Minutes",
    EXTRACT( SECOND FROM @time ) AS "Seconds",
    EXTRACT( MICROSECOND FROM @time ) AS "Microseconds";

Result:

+-------+---------+---------+--------------+
| Hours | Minutes | Seconds | Microseconds |
+-------+---------+---------+--------------+
|    12 |      17 |      38 |       123456 |
+-------+---------+---------+--------------+

The DATE_FORMAT() Function

The DATE_FORMAT() function allows us to format a date value based on a format string. The format string specifies how the date/time should be formatted.

However, DATE_FORMAT() doesn’t accept a TIME value, so we will need to pass a DATETIME value.

SET @datetime = '2020-12-30 12:17:38.123456';
SELECT
    DATE_FORMAT( @datetime , '%H') AS "Hours",
    DATE_FORMAT( @datetime , '%i') AS "Minutes",
    DATE_FORMAT( @datetime , '%s') AS "Seconds",
    DATE_FORMAT( @datetime , '%f') AS "Microseconds";

Result:

+-------+---------+---------+--------------+
| Hours | Minutes | Seconds | Microseconds |
+-------+---------+---------+--------------+
| 12    | 17      | 38      | 123456       |
+-------+---------+---------+--------------+

As mentioned, passing a TIME value doesn’t work with this function:

SET @time = '12:17:38.123456';
SELECT
    DATE_FORMAT( @time , '%H') AS "Hours",
    DATE_FORMAT( @time , '%i') AS "Minutes",
    DATE_FORMAT( @time , '%s') AS "Seconds",
    DATE_FORMAT( @time , '%f') AS "Microseconds";

Result:

+-------+---------+---------+--------------+
| Hours | Minutes | Seconds | Microseconds |
+-------+---------+---------+--------------+
| NULL  | NULL    | NULL    | NULL         |
+-------+---------+---------+--------------+
1 row in set, 4 warnings (0.00 sec)

Let’s see the warning:

SHOW WARNINGS;

Result:

+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '12:17:38.123456' |
| Warning | 1292 | Incorrect datetime value: '12:17:38.123456' |
| Warning | 1292 | Incorrect datetime value: '12:17:38.123456' |
| Warning | 1292 | Incorrect datetime value: '12:17:38.123456' |
+---------+------+---------------------------------------------+

See MySQL Date Format Specifiers for a full list of format specifiers that can be used with DATE_FORMAT().

The TIME_FORMAT() Function

The TIME_FORMAT() function is similar to DATE_FORMAT() except that it allows us to format time values.

We can use the same format specifiers that we used with DATE_FORMAT() in the previous example:

SET @time = '12:17:38.123456';
SELECT 
    TIME_FORMAT( @time , '%H' ) AS "Hours",
    TIME_FORMAT( @time , '%i' ) AS "Minutes",
    TIME_FORMAT( @time , '%s' ) AS "Seconds",
    TIME_FORMAT( @time , '%f' ) AS "Microseconds";

Result:

+-------+---------+---------+--------------+
| Hours | Minutes | Seconds | Microseconds |
+-------+---------+---------+--------------+
| 12    | 17      | 38      | 123456       |
+-------+---------+---------+--------------+

Both TIME_FORMAT() and DATE_FORMAT() allow us to return the time components separately, but in the same column, simply by the way we construct the format string.

Example:

SET @time = '12:17:38.123456';
SELECT TIME_FORMAT( @time , '%H %i %s %f' );

Result:

+--------------------------------------+
| TIME_FORMAT( @time , '%H %i %s %f' ) |
+--------------------------------------+
| 12 17 38 123456                      |
+--------------------------------------+

The TIME_FORMAT() function accepts a subset of the format strings that can be used with the DATE_FORMAT() function. Specifically, it only accepts format strings for hours, minutes, seconds, and microseconds. Any other format specifier results in null or 0 being returned.

There are several possible format specifiers for the hour part. The format specifier used above returns the hour with two digits between 00 and 23. See MySQL Date Format Specifiers for a full list of format specifiers that can be used with DATE_FORMAT(). As mentioned, the TIME_FORMAT() function only accepts format specifiers for hours, minutes, and seconds/sub seconds.