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 returns838
)EXTRACT(HOUR FROM ...)
returns a value between0
and23
. If the hour is outside this range,EXTRACT()
will iterate through0
and23
as many times as necessary to return a value between0
and23
.
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 | +-------------------+--------------------------------+