In MariaDB, HOUR() is a built-in date and time function that returns the hour from a given time expression.
It accepts one argument, which is the time you want to extract the hour from.
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.
The return value is always positive, even if a negative time is provided.
Syntax
The syntax goes like this:
HOUR(time)Where time is the time expression to get the hour from.
Example
Here’s an example:
SELECT HOUR('10:30:45');Result:
+------------------+
| HOUR('10:30:45') |
+------------------+
| 10 |
+------------------+
Datetime Values
It also works with datetime values:
SELECT HOUR('2030-02-01 10:30:45');Result:
+-----------------------------+
| HOUR('2030-02-01 10:30:45') |
+-----------------------------+
| 10 |
+-----------------------------+
Larger Hours
TIME values can be in the range '-838:59:59.999999' to '838:59:59.999999'.
Therefore, the hour portion can be much higher than 23:
SELECT HOUR('578:30:45');Result:
+-------------------+
| HOUR('578:30:45') |
+-------------------+
| 578 |
+-------------------+
Negative Times
Negative times return a positive result.
Example
SELECT HOUR('-578:30:45');Result:
+--------------------+
| HOUR('-578:30:45') |
+--------------------+
| 578 |
+--------------------+
Out of Range Hours
Time values outside the range '-838:59:59.999999' to '838:59:59.999999' return 838.
Example:
SELECT HOUR('978:30:45');Result (using vertical output):
+-------------------+
| HOUR('978:30:45') |
+-------------------+
| 838 |
+-------------------+
Current Date
We can pass NOW() as the datetime argument to use the current time:
SELECT
NOW(),
HOUR(NOW());Result:
+---------------------+-------------+ | NOW() | HOUR(NOW()) | +---------------------+-------------+ | 2021-05-16 10:50:02 | 10 | +---------------------+-------------+
Invalid Arguments
When passed an invalid argument, HOUR() returns null:
SELECT HOUR('Ten Thirty AM');Result:
+-----------------------+
| HOUR('Ten Thirty AM') |
+-----------------------+
| NULL |
+-----------------------+
Missing Argument
Calling HOUR() with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT HOUR();Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1
And another example:
SELECT HOUR('10:30:45', '06:30:45');Result:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' '06:30:45')' at line 1