MySQL HOUR( ) Function

Summary: in this tutorial, you will learn how to use the MySQL HOUR() function to get the hour for a time value.

Introduction to MySQL HOUR() function

The HOUR() function allows you to extract the hour component of a time value.

Here’s the basic syntax of the HOUR() function:

HOUR(time)Code language: SQL (Structured Query Language) (sql)

In this syntax:

time: The time that you want to extract the hour component. The time value has the data type is TIME.

The HOUR() function returns an integer that represents the hour component of the time.

If the time represents the time of the day, the HOUR() function returns a value between 0 and 23. But if the time value is larger, the HOUR() function can return values greater than 23.

If the time is NULL, the HOUR() function returns NULL.

MySQL HOUR() function examples

Let’s take some examples of using the HOUR() function.

1) Simple HOUR() function example

The following example uses the HOUR() function to get the hour from the time ’10:45:20′:

SELECT HOUR('10:45:20');Code language: SQL (Structured Query Language) (sql)

Output:

+------------------+
| HOUR('10:45:20') |
+------------------+
|               10 |
+------------------+
1 row in set (0.01 sec)Code language: plaintext (plaintext)

2) Using HOUR() function with table data

First, create a new table called orders with the following structure:

CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATETIME NOT NULL
);Code language: SQL (Structured Query Language) (sql)

The orders table is simplified for brevity purposes.

Second, insert some rows into the orders table:

INSERT INTO orders (order_date) 
VALUES
    ('2023-10-18 08:15:00'),
    ('2023-10-18 08:30:00'),
    ('2023-10-18 14:45:00'),
    ('2023-10-18 14:00:00'),
    ('2023-10-18 15:30:00');Code language: SQL (Structured Query Language) (sql)

Third, get the number of orders by hours using the HOUR() function:

SELECT 
  HOUR(order_date) AS order_hour, 
  COUNT(*) AS order_count 
FROM 
  orders 
GROUP BY 
  order_hour;Code language: SQL (Structured Query Language) (sql)

Output:

+------------+-------------+
| order_hour | order_count |
+------------+-------------+
|          8 |           2 |
|         14 |           2 |
|         15 |           1 |
+------------+-------------+
3 rows in set (0.00 sec)Code language: plaintext (plaintext)

Summary

Use the HOUR() function to get the hour component of a specific time.

Was this tutorial helpful?