MySQL - HOUR() Function

The MySQL HOUR() function is used to retrieve and return the hour of time from the given date time expression.

The HOUR() function accepts the time string as an argument and retrieves the hours part of it. Since the hours are counted in 24-hour time format, the range of result obtained from this function always lies between 00 to 23.

Note that this function does not calculate the duration from the given time in the form of hours, but just gets the hours from the timestamp argument passed to it.

Syntax

Following is the syntax of the above function −

HOUR(time);

Parameters

This method accepts the time expression from which you need to extract the hour as a parameter.

Return value

This function returns the hour of time from the given date time expression.

Example

Following example demonstrates the usage of the MySQL HOUR() function −

SELECT HOUR('00:00:00 09:40:45.2300') As Result;

Output

Following is the output −

Result
9

Example

We can also pass the date-time expression as an argument to this function −

SELECT HOUR('2015-09-05 22:40:45.2300') As Result;

Output

Following is the output −

Result
22

Example

In the following example we are retrieving the hours value from the current timestamp −

SELECT HOUR(CURRENT_TIMESTAMP) As Result;

Output

Following is the output −

Result
14

Example

We can pass the result of the NOW() function as an argument to this function −

SELECT HOUR(NOW()) As Result;

Output

Following is the output −

Result
14

Example

In this example, we have created a table named ORDERS using the following CREATE TABLE query 

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Now, insert the following records into the ORDERS table using the INSERT statement −

INSERT INTO ORDERS VALUES 
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);

Execute the below query to fetch all the inserted records in the above-created table −

Select * From ORDERS;

Following is the ORDERS table −

OID DATE CUSTOMER_ID AMOUNT
102 2009-10-08 00:00:00 3 3000.00
100 2009-10-08 00:00:00 3 1500.00
101 2009-11-20 00:00:00 2 1560.00
103 2008-05-20 00:00:00 4 2060.00

Now, we use the MySQL HOUR() function to extract the hour component from all the datetime values in the DATE column of ORDERS table −

SELECT OID, DATE, HOUR(DATE) As Hour FROM ORDERS;

Output

The output is displayed as follows −

OID DATE Hour
102 2009-10-08 00:00:00 0
100 2009-10-08 00:00:00 0
101 2009-11-20 00:00:00 0
103 2008-05-20 00:00:00 0
Advertisements