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

The HOUR() Function

The HOUR() function returns the hour for a given TIME or DATETIME expression. It accepts one argument, which is the time you want to extract the hour from.

Example:

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

Result:

+------------------+
| HOUR('03:45:30') |
+------------------+
|                3 |
+------------------+

For time-of-day values, it returns the hour as a number in the range 0 to 23. However, the range of TIME values can be much larger, and therefore, the returned value can be much higher than 23. Specifically, TIME values can be in the range '-838:59:59.999999' to '838:59:59.999999'.

Here’s an example with a larger hour part:

SELECT HOUR('838:45:30');

Result:

+-------------------+
| HOUR('838:45:30') |
+-------------------+
|               838 |
+-------------------+

If the time is outside the accepted range for TIME values, the return value is 838 with a warning.

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

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

Example:

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

Result:

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

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 hour (as well as minutes and seconds) from the time. There are various options for returning the hour.

Here’s an example that returns the hour in various forms:

SELECT 
    TIME_FORMAT('18:45:30', '%H') AS '%H',
    TIME_FORMAT('18:45:30', '%h') AS '%h',
    TIME_FORMAT('18:45:30', '%I') AS '%I',
    TIME_FORMAT('18:45:30', '%k') AS '%k',
    TIME_FORMAT('18:45:30', '%l') AS '%l';

Result:

+------+------+------+------+------+
| %H   | %h   | %I   | %k   | %l   |
+------+------+------+------+------+
| 18   | 06   | 06   | 18   | 6    |
+------+------+------+------+------+

Here’s a description of each of those format specifiers:

Format Specifier	Description
%H	Hour with 2 digits between 00-23.
%h	Hour with 2 digits between 01-12.
%I	Hour with 2 digits between 01-12.
%k	Hour with 1 digits between 0-23.
%l	Hour with 1 digits between 1-12.

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 18:45:30', '%H') AS '%H',
    DATE_FORMAT('2023-01-01 18:45:30', '%h') AS '%h',
    DATE_FORMAT('2023-01-01 18:45:30', '%I') AS '%I',
    DATE_FORMAT('2023-01-01 18:45:30', '%k') AS '%k',
    DATE_FORMAT('2023-01-01 18:45:30', '%l') AS '%l';

Result:

+------+------+------+------+------+
| %H   | %h   | %I   | %k   | %l   |
+------+------+------+------+------+
| 18   | 06   | 06   | 18   | 6    |
+------+------+------+------+------+

Passing just a TIME value doesn’t quite cut it with this function:

SELECT 
    DATE_FORMAT('18:45:30', '%H') AS '%H',
    DATE_FORMAT('18:45:30', '%h') AS '%h',
    DATE_FORMAT('18:45:30', '%I') AS '%I',
    DATE_FORMAT('18:45:30', '%k') AS '%k',
    DATE_FORMAT('18:45:30', '%l') AS '%l';

Result:

+------+------+------+------+------+
| %H   | %h   | %I   | %k   | %l   |
+------+------+------+------+------+
| NULL | NULL | NULL | NULL | NULL |
+------+------+------+------+------+
1 row in set, 5 warnings (0.000 sec)

Show the warnings:

SHOW WARNINGS;

Result:

+---------+------+--------------------------------------+
| Level   | Code | Message                              |
+---------+------+--------------------------------------+
| Warning | 1292 | Incorrect datetime value: '18:45:30' |
| Warning | 1292 | Incorrect datetime value: '18:45:30' |
| Warning | 1292 | Incorrect datetime value: '18:45:30' |
| Warning | 1292 | Incorrect datetime value: '18:45:30' |
| Warning | 1292 | Incorrect datetime value: '18:45:30' |
+---------+------+--------------------------------------+