The MySQL HOUR() function is used to return the hour component from a time value.
The return value for a time-of-day value will be between 0 and 23, however the range returned by this function could be much larger, due to the fact that a time data type isn’t limited to just a time of day (it can also be used to represent elapsed time or a time interval between two events). This means that the returned value could be greater than 24 hours or even a negative value.
Syntax
The syntax of this function goes like this:
HOUR(time)
Where time is the time value that you want to extract the hour component from.
Example 1 – Basic Usage
Here’s an example to demonstrate.
SELECT HOUR('10:35:27');
Result:
+------------------+
| HOUR('10:35:27') |
+------------------+
| 10 |
+------------------+
Example 2 – Greater than 24 Hours
As mentioned, if the time value is used to represent elapsed time or a time interval between two events, it could have an hour component larger than 24 hours. So the following example is perfectly valid.
SELECT HOUR('310:35:27');
Result:
+-------------------+
| HOUR('310:35:27') |
+-------------------+
| 310 |
+-------------------+
Example 3 – Negative Values
Negative values are also perfectly valid for the time data type. However, note that the HOUR() function will return it as a positive value:
SELECT HOUR('-310:35:27');
Result:
+--------------------+
| HOUR('-310:35:27') |
+--------------------+
| 310 |
+--------------------+
If this is a problem and you need negative values returned as negative values, the EXTRACT() function might be what you’re looking for:
SELECT EXTRACT(HOUR FROM '-310:35:27');
Result:
+---------------------------------+ | EXTRACT(HOUR FROM '-310:35:27') | +---------------------------------+ | -310 | +---------------------------------+