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

The MINUTE() Function

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

Example:

SELECT MINUTE('03:45:30');

Result:

+--------------------+
| MINUTE('03:45:30') |
+--------------------+
|                 45 |
+--------------------+

TIME values can be in the range '-838:59:59.999999' to '838:59:59.999999'. So, although the hours part can be much higher than 23, the minutes part can only be in the range 0 to 59.

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

Here’s a negative time value with a larger hour part:

SELECT MINUTE('-838:59:59');

Result:

+----------------------+
| MINUTE('-838:59:59') |
+----------------------+
|                   59 |
+----------------------+

The minutes 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 minutes (as well as other units) from the time value.

Example:

SELECT EXTRACT(MINUTE FROM '10:47:01');

Result:

+---------------------------------+
| EXTRACT(MINUTE FROM '10:47:01') |
+---------------------------------+
|                              47 |
+---------------------------------+

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 minutes (as well as hours and seconds) from the time.

Using the %i format specifier returns the minutes with two digits:

SELECT TIME_FORMAT('10:07:30', '%i');

Result:

+-------------------------------+
| TIME_FORMAT('10:07:30', '%i') |
+-------------------------------+
| 07                            |
+-------------------------------+

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. 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', '%i');

Result:

+------------------------------------------+
| DATE_FORMAT('2023-01-01 10:07:30', '%i') |
+------------------------------------------+
| 07                                       |
+------------------------------------------+

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

SELECT DATE_FORMAT('10:07:30', '%i') AS '%i';

Result:

+------+
| %i   |
+------+
| 00   |
+------+