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_formatis set toSTATEMENT.