MariaDB has several functions that enable you to extract various date and time parts from date/time values. You can use these to separate each date/time component into its own column if required.
Below are four ways to extract the hours, minutes, and seconds from a time value in MariaDB. As a bonus, we’ll also include microseconds.
The HOUR(), MINUTE(), SECOND() and MICROSECOND() Functions
The HOUR(), MINUTE(), and SECOND() functions extract the hours, minutes, and seconds from a date or datetime value respectively.
And if you’re working with a higher precision, you can use the MICROSECOND() function to return the microseconds part.
Here’s an example of using these functions to return each time component in its own column:
SELECT
HOUR('10:45:30.123456') AS "Hours",
MINUTE('10:45:30.123456') AS "Minutes",
SECOND('10:45:30.123456') AS "Seconds",
MICROSECOND('10:45:30.123456') AS "Microseconds";Result:
+-------+---------+---------+--------------+ | Hours | Minutes | Seconds | Microseconds | +-------+---------+---------+--------------+ | 10 | 45 | 30 | 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, the return values are still positive.
Here’s a negative time value:
SELECT
HOUR('-10:45:30.123456') AS "Hours",
MINUTE('-10:45:30.123456') AS "Minutes",
SECOND('-10:45:30.123456') AS "Seconds",
MICROSECOND('-10:45:30.123456') AS "Microseconds";Result:
+-------+---------+---------+--------------+ | Hours | Minutes | Seconds | Microseconds | +-------+---------+---------+--------------+ | 10 | 45 | 30 | 123456 | +-------+---------+---------+--------------+
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 hours, minutes, seconds, and microseconds from the time value.
Example:
SELECT
EXTRACT(HOUR FROM '10:45:30.123456') AS "Hours",
EXTRACT(MINUTE FROM '10:45:30.123456') AS "Minutes",
EXTRACT(SECOND FROM '10:45:30.123456') AS "Seconds",
EXTRACT(MICROSECOND FROM '10:45:30.123456') AS "Microseconds";Result:
+-------+---------+---------+--------------+ | Hours | Minutes | Seconds | Microseconds | +-------+---------+---------+--------------+ | 10 | 45 | 30 | 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 various time parts separately.
Example:
SELECT
TIME_FORMAT('10:07:30.123456', '%H') AS "Hours",
TIME_FORMAT('10:07:30.123456', '%i') AS "Minutes",
TIME_FORMAT('10:07:30.123456', '%s') AS "Seconds",
TIME_FORMAT('10:07:30.123456', '%f') AS "Microseconds";Result:
+-------+---------+---------+--------------+ | Hours | Minutes | Seconds | Microseconds | +-------+---------+---------+--------------+ | 10 | 07 | 30 | 123456 | +-------+---------+---------+--------------+
It’s also possible to return all time components separately, but in the same column.
Here’s an example of what I mean:
SELECT TIME_FORMAT('10:07:30.123456', '%H %i %s %f');Result:
+-----------------------------------------------+
| TIME_FORMAT('10:07:30.123456', '%H %i %s %f') |
+-----------------------------------------------+
| 10 07 30 123456 |
+-----------------------------------------------+
The TIME_FORMAT() function accepts a subset of the format strings that can be used with the DATE_FORMAT() function.
There are several possible format specifiers for the hour part. The format specifier used above returns the hour with two digits between 00 and 23. 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.123456', '%H') AS "Hours",
DATE_FORMAT('2023-01-01 10:07:30.123456', '%i') AS "Minutes",
DATE_FORMAT('2023-01-01 10:07:30.123456', '%s') AS "Seconds",
DATE_FORMAT('2023-01-01 10:07:30.123456', '%f') AS "Microseconds";Result:
+-------+---------+---------+--------------+ | Hours | Minutes | Seconds | Microseconds | +-------+---------+---------+--------------+ | 10 | 07 | 30 | 123456 | +-------+---------+---------+--------------+
Passing just a TIME value doesn’t work with this function:
SELECT
DATE_FORMAT('10:07:30.123456', '%H') AS "Hours",
DATE_FORMAT('10:07:30.123456', '%i') AS "Minutes",
DATE_FORMAT('10:07:30.123456', '%s') AS "Seconds",
DATE_FORMAT('10:07:30.123456', '%f') AS "Microseconds";Result:
+-------+---------+---------+--------------+ | Hours | Minutes | Seconds | Microseconds | +-------+---------+---------+--------------+ | NULL | NULL | NULL | NULL | +-------+---------+---------+--------------+ 1 row in set, 4 warnings (0.003 sec)
Let’s see the warning:
SHOW WARNINGS;Result:
+---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1292 | Incorrect datetime value: '10:07:30.123456' | | Warning | 1292 | Incorrect datetime value: '10:07:30.123456' | | Warning | 1292 | Incorrect datetime value: '10:07:30.123456' | | Warning | 1292 | Incorrect datetime value: '10:07:30.123456' | +---------+------+---------------------------------------------+