MySQL HOUR() Function Examples

MySQL HOUR() function; In this tutorial, We are going to show you how to use HOUR() function of MySQL with the help of it’s syntax and examples.

And as well as, will take database table related examples like we find the last month records from the database table who has login greater than 10 hours, find the current month records who have login greater than 8 hours.

MySQL HOUR() Function

In MySQL, The hour() function is used to get the hour from the given DateTime.

The return value for time value will be between 0 and 23, however, the range given by this function can be very large

Syntax

The basic syntax of MySQL HOUR() function is:

HOUR(time)

The time here is the value that you want to extract the hour from the given DateTime value.

Example-1

Let’s take the first example of hour() function:

SELECT HOUR('11:40:30');

Output-1

+------------------+
| HOUR('11:40:30') |
+------------------+
|               11 |
+------------------+

Example-2

As mentioned, if the time value is used to represent the time or time interval between the two events, then this one-hour component may be larger than 24 hours. So the following example is completely valid.

SELECT HOUR('280:40:28');

Output-2

+-------------------+
| HOUR('280:40:28') |
+-------------------+
|               280 |
+-------------------+

Example-3

Negative values are also perfectly valid for time data types. However, note that the HOUR () function will return it as a positive value:

SELECT HOUR('-310:35:27');

Output-3

+--------------------+
| HOUR('-310:35:27') |
+--------------------+
|                310 |
+--------------------+

Example-4

Let’s take a another example with MySQL NOW() function:

SELECT HOUR(NOW());

Output-4

+--------------------+
| HOUR(NOW)          |
+--------------------+
|                16  |
+--------------------+

Example-5

Let’s take a new example with database table employees. We will fetch users who have login time greater than 10 hours. So below the given MySQL query is used to getting the last month records from the database table who has login greater than 10 hours:

 ====================================For Current Month============================= 
 SELECT name, created_at as created_date 
 FROM employees
 WHERE HOUR(login_time) > 10 AND
 MONTH(created_at) = MONTH(NOW()) - 1 ORDER BY `id` DESC ;

====================================For Current Month=============================

 SELECT name, created_at as created_date 
 FROM employees
 WHERE HOUR(login_time) > 8 AND
 MONTH(created_at) = MONTH(NOW()) ORDER BY `id` DESC ;

Output-5

Example-6

You can easily add hours for now() using the hour() function. Let’s see the example below

SELECT NOW(), NOW() + INTERVAL 8 HOUR

Output-6

+--------------------+--------------------------
| NOW                | NOW() + INTERVAL 8 HOUR |
+--------------------+--------------------------
| 2019-07-17 10:00:00| 2019-07-18 04:00:00     |
+--------------------+--------------------------

Conclusion

Here, you have learned how to use MySQL HOUR() function.