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 |
+---------------------------------+