MySQL HOUR() function

HOUR() function

MySQL HOUR() returns the HOUR of a time. The return value is within the range of 0 to 23 for time-of-day values. The range of time values may be larger than 23. It provides a way to isolate the hour part of a time for various analysis and manipulation purposes.

This function is useful in -

• HOUR() is used to isolate and analyze the hour part of a time, enabling insights into patterns or trends based on specific hours.
• The function is valuable for filtering data based on specific hours, allowing you to analyze or present data within certain time windows.
• HOUR() aids in scheduling tasks or activities that need to occur at specific hours, such as reminders or alerts.
• In graphical presentations, HOUR() can be used to label axes or data points with the hour values, enhancing clarity.
• HOUR() can be used to categorize data into time slots, making it easier to analyze trends or occurrences during different parts of the day.
• For tracking events or activities that occur at specific hours of the day, HOUR() helps identify when those events take place.
• HOUR() is used for various data analysis tasks that involve time-based metrics, helping to segment and analyze data based on specific hours.

Syntax:

HOUR(time)

Where time is a time.

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL HOUR() function

If we get to return the HOUR from the given time 15:13:46, the following SQL can be used -

Code:

SELECT HOUR('15:13:46');

Output:

mysql> SELECT HOUR('15:13:46');
+------------------+
| HOUR('15:13:46') |
+------------------+
|               15 | 
+------------------+
1 row in set (0.01 sec)

Sample table: wages_emp

+--------+----------+------------+-----------+----------+---------+-------+
| emp_id | emp_name | wor_dt     | time_from | time_to  | hr_rate | wages |
+--------+----------+------------+-----------+----------+---------+-------+
| E001   | RAMON    | 2010-02-10 | 10:00:00  | 14:00:00 |      50 |   200 | 
| E002   | SILTON   | 2010-05-05 | 11:00:00  | 16:00:00 |      60 |   300 | 
| E001   | RAMON    | 2010-07-11 | 11:00:00  | 16:00:00 |      60 |   300 | 
| E001   | RAMON    | 2011-09-15 | 08:00:00  | 12:00:00 |      75 |   300 | 
+--------+----------+------------+-----------+----------+---------+-------+

MySQL HOUR() function with WHERE clause

If we want to know the information for the worker who starts the work after 10 a.m, the following sql can be used -

Code:

SELECT * FROM wages_emp
WHERE HOUR(time_from)>10;

Output:

+--------+----------+------------+-----------+----------+---------+-------+
| emp_id | emp_name | wor_dt     | time_from | time_to  | hr_rate | wages |
+--------+----------+------------+-----------+----------+---------+-------+
| E002   | SILTON   | 2010-05-05 | 11:00:00  | 16:00:00 |      60 |   300 | 
| E001   | RAMON    | 2010-07-11 | 11:00:00  | 16:00:00 |      60 |   300 | 
+--------+----------+------------+-----------+----------+---------+-------+
2 rows in set (0.00 sec)

MySQL HOUR() function with BETWEEN

If we want to know the information for the workers who works for a duration of 2 to 4 hours, the following sql can be used -

Code:

SELECT emp_name,wor_dt,time_from,time_to,
HOUR(time_to)-HOUR(time_from) as duration,wages
FROM wages_emp
WHERE HOUR(time_to)-HOUR(time_from)
BETWEEN 2 AND 4;

Output:

+----------+------------+-----------+----------+----------+-------+
| emp_name | wor_dt     | time_from | time_to  | duration | wages |
+----------+------------+-----------+----------+----------+-------+
| RAMON    | 2010-02-10 | 10:00:00  | 14:00:00 |        4 |   200 | 
| RAMON    | 2011-09-15 | 08:00:00  | 12:00:00 |        4 |   300 | 
+----------+------------+-----------+----------+----------+-------+
2 rows in set (0.00 sec)

MySQL HOUR() function with IN

If we want to know the information for the workers who usually starts work at 8 or 9 or 11 a.m. to their working days, the following sql can be used -

Code:

SELECT * 
FROM wages_emp
WHERE HOUR(time_from)
IN(8,9,11);

Output:

+--------+----------+------------+-----------+----------+---------+-------+
| emp_id | emp_name | wor_dt     | time_from | time_to  | hr_rate | wages |
+--------+----------+------------+-----------+----------+---------+-------+
| E002   | SILTON   | 2010-05-05 | 11:00:00  | 16:00:00 |      60 |   300 | 
| E001   | RAMON    | 2010-07-11 | 11:00:00  | 16:00:00 |      60 |   300 | 
| E001   | RAMON    | 2011-09-15 | 08:00:00  | 12:00:00 |      75 |   300 | 
+--------+----------+------------+-----------+----------+---------+-------+
3 rows in set (0.00 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: GET_FORMAT()
Next: LAST_DAY()