When working with MySQL, you can use the TIME() function to extract the time part from a time or datetime value.

The way it works is, you pass the time/datetime expression in as an argument, and TIME() will return the time part.

Syntax

The syntax goes like this:

TIME(expr)

Where expr is the time/datetime expression that you want the time part extracted from.

Basic Example

Here’s an example using a datetime value.

SELECT TIME('2021-01-03 11:15:45');

Result:

+-----------------------------+
| TIME('2021-01-03 11:15:45') |
+-----------------------------+
| 11:15:45                    |
+-----------------------------+

Fractional Seconds

Here’s an example where the datetime value also contains a fractional seconds part.

SELECT TIME('2021-01-03 11:15:45.123456');

Result:

+------------------------------------+
| TIME('2021-01-03 11:15:45.123456') |
+------------------------------------+
| 11:15:45.123456                    |
+------------------------------------+

Omitting the Seconds Part

Here’s an example where the seconds part is omitted from the initial value.

SELECT TIME('2021-01-03 11:15');

Result:

+--------------------------+
| TIME('2021-01-03 11:15') |
+--------------------------+
| 11:15:00                 |
+--------------------------+

In this case, the seconds part is still returned, even though it was omitted from the initial value.

Extracting the Time from a ‘time’ Value

As mentioned, the first argument can be a time value itself (i.e. it doesn’t have to be a datetime value).

SELECT TIME('11:15');

Result:

+---------------+
| TIME('11:15') |
+---------------+
| 11:15:00      |
+---------------+

Statement-Based Replication – Warning

The MySQL documentations states the following about the TIME() function:

This function is unsafe for statement-based replication. A warning is logged if you use this function when binlog_format is set to STATEMENT.