Below are 4 functions that enable you to return the microseconds part from a time value in MariaDB.

The MICROSECOND() Function

The MICROSECOND() function returns the microseconds part of a given TIME or DATETIME expression. It accepts one argument, which is the time you want to extract the microseconds from.

Example:

SELECT MICROSECOND('10:45:30.123456');

Result:

+--------------------------------+
| MICROSECOND('10:45:30.123456') |
+--------------------------------+
|                         123456 |
+--------------------------------+

TIME values can be in the range '-838:59:59.999999' to '838:59:59.999999'.

TIME values can also be negative. In such cases, MICROSECOND() returns a positive value.

Here’s a negative time value with a microseconds part:

SELECT MICROSECOND('-838:59:59.999999');

Result:

+----------------------------------+
| MICROSECOND('-838:59:59.999999') |
+----------------------------------+
|                           999999 |
+----------------------------------+

The microseconds part is extracted as expected.

The EXTRACT() Function

The EXTRACT() function allows you to extract a specified unit from the date/time value. Therefore, you can use it to extract the microseconds (as well as other units) from the time value.

Example:

SELECT EXTRACT(MICROSECOND FROM '10:45:30.123456');

Result:

+---------------------------------------------+
| EXTRACT(MICROSECOND FROM '10:45:30.123456') |
+---------------------------------------------+
|                                      123456 |
+---------------------------------------------+

The TIME_FORMAT() Function

The TIME_FORMAT() function allows you to format a time value based on a format string. The format string specifies how the time should be formatted.

You can therefore use this function to return the microseconds (as well as hours, minutes and seconds) from the time.

The %f format specifier returns the microseconds:

SELECT TIME_FORMAT('10:07:30.003456', '%f');

Result:

+--------------------------------------+
| TIME_FORMAT('10:07:30.003456', '%f') |
+--------------------------------------+
| 003456                               |
+--------------------------------------+

You can also use the $s or $S format specifier to return the seconds part:

SELECT 
    TIME_FORMAT('10:07:30.123456', '%s') AS "Seconds",
    TIME_FORMAT('10:07:30.123456', '%f') AS "Microseconds",
    TIME_FORMAT('10:07:30.123456', '%s.%f') AS "Both";

Result:

+---------+--------------+-----------+
| Seconds | Microseconds | Both      |
+---------+--------------+-----------+
| 30      | 123456       | 30.123456 |
+---------+--------------+-----------+

The TIME_FORMAT() function accepts a subset of the format strings that can be used with the DATE_FORMAT() function. See MariaDB Format Strings for a full list of format strings/specifiers that can be used with DATE_FORMAT(). The TIME_FORMAT() function only accepts format specifiers for hours, minutes, and seconds/sub seconds. Any other format specifier results in null or 0 being returned.

The DATE_FORMAT() Function

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

We can therefore use the same format specifiers that we used with the TIME_FORMAT() function in the previous example. However, DATE_FORMAT() doesn’t accept a TIME value, so we will need to pass a DATETIME value.

SELECT DATE_FORMAT('2023-01-01 10:07:30.007123', '%f');

Result:

+-------------------------------------------------+
| DATE_FORMAT('2023-01-01 10:07:30.007123', '%f') |
+-------------------------------------------------+
| 007123                                          |
+-------------------------------------------------+

Passing just a TIME value doesn’t work with this function:

SELECT DATE_FORMAT('10:07:30.007123', '%f');

Result:

+--------------------------------------+
| DATE_FORMAT('10:07:30.007123', '%f') |
+--------------------------------------+
| NULL                                 |
+--------------------------------------+
1 row in set, 1 warning (0.003 sec)

Let’s see the warning:

SHOW WARNINGS;

Result:

+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '10:07:30.007123' |
+---------+------+---------------------------------------------+