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