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

The SECOND() Function

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

Example:

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

Result:

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

TIME values can be in the range '-838:59:59.999999' to '838:59:59.999999'. This includes microseconds, but SECOND() returns just the seconds part (there’s a MICROSECOND() function for returning microseconds).

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

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

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

Result:

+-----------------------------+
| SECOND('-838:59:59.999999') |
+-----------------------------+
|                          59 |
+-----------------------------+

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

Example:

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

Result:

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

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

The %s and %S format specifiers both return the seconds with two digits:

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

Result:

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

You can also use the $f format specifier to return six digit sub seconds:

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

Result:

+--------------+-----------+
| Microseconds | Both      |
+--------------+-----------+
| 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', '%s');

Result:

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

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

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

Result:

+------+
| %s   |
+------+
| 00   |
+------+