MariaDB has an HOUR() function that extracts the hour portion from a time value. MariaDB also has an EXTRACT() function that can also extract the hour portion from a time value.

However, these functions don’t always return the same result.

Read on to see the difference between HOUR() and EXTRACT(HOUR FROM ...) in MariaDB.

The Difference

The HOUR() and EXTRACT(HOUR FROM ...) both return the same value when the time expression is a time-of-day expression. That is, if the hour part is between 0 and 23, then they return the same result.

The difference manifests itself when the hour part is larger than 23.

TIME values can be in the range '-838:59:59.999999' to '838:59:59.999999', but a time-of-day value can only be between 0 and 23.

If the time expression is outside the 0 and 23 range:

• HOUR() returns the actual hour part from the expression (as long as its within the range '-838:59:59.999999' to '838:59:59.999999' – if outside this range, it returns 838)
• EXTRACT(HOUR FROM ...) returns a value between 0 and 23. If the hour is outside this range, EXTRACT() will iterate through 0 and 23 as many times as necessary to return a value between 0 and 23.

Example

Here’s an example to demonstrate.

SELECT 
    HOUR('24:00:00'),
    EXTRACT(HOUR FROM '24:00:00');

Result:

+------------------+-------------------------------+
| HOUR('24:00:00') | EXTRACT(HOUR FROM '24:00:00') |
+------------------+-------------------------------+
|               24 |                             0 |
+------------------+-------------------------------+

The HOUR() function returns the actual hour that was provided, whereas EXTRACT() returns 0. This is because 24 is higher than 23, which is the highest value EXTRACT() will return for the hour portion. In this case, EXTRACT() starts the count again, starting at 0.

It will keep doing this as many times as it needs to, so that the result is always between 0 and 23.

Here’s another example with a much larger hour part:

SELECT 
    HOUR('742:00:00'),
    EXTRACT(HOUR FROM '742:00:00');

Result:

+-------------------+--------------------------------+
| HOUR('742:00:00') | EXTRACT(HOUR FROM '742:00:00') |
+-------------------+--------------------------------+
|               742 |                             22 |
+-------------------+--------------------------------+