Do you think IIT Guwahati certified course can help you in your career?
Introduction
SQL (Structured Query Language) is a famous programming language for managing our databases. MySQL is a relational database management system. As a powerful database platform, it provides numerous functions to alter and retrieve information from databases. Some of these functions that we will discuss are the MINUTE(), MICROSECOND() and HOUR() functions in MySQL.
In this blog, we will study MINUTE(), MICROSECOND() and HOUR() functions in MySQL. These functions extract specific parts of a time value in different units. We will also discuss some programming code for better understanding. Are you ready to learn? Let's get started!
MINUTE() Function
The MINUTE() function in MySQL is used to get the minute component for a given date-time value. As the value of a minute can be from 0 to 59, the output range for the minute function is also the same. The syntax of the MINUTE() function is as follows:
SELECT MINUTE(date-time_value)
The above command takes the date-time value as the input parameter and returns a value between 0 to 59. Let’s see some examples to have better clarity.
Example-1:
SELECT MINUTE ("2023-05-25 08:06:00");
Output –
6
The output is the minute value in the given time, i.e., 6.
Example-2:
SELECT MINUTE("10:46:17");
Output –
46
The output is the minute value in the given time, i.e., 46.
Example-3:
SELECT MINUTE(NOW());
Output –
The MINUTE(NOW()) is used to get the current minute value. As we can see in the above image, the output is 13 because the action time is 20:13:32.
The MINUTE() function extracts the minute component from a time value. For example, we can use it to retrieve all orders placed during a particular minute of the hour or calculate the average duration of events in minutes.
HOUR() Function
MySQL's HOUR() function is used to get the hour component from a given date-time value. The output range for the hour function is integer values from 0 to 23. The syntax of the HOUR() function is as follows:
SELECT HOUR(data-time_value)
The above command takes the date-time value as the input parameter and returns a value between 0 to 23. Let’s see some examples to have better clarity.
Example-1:
SELECT HOUR("2023-05-25 10:24:01.987654");
Output –
10
Example-2:
SELECT HOUR("2023-05-25 21:05:15");
Output –
21
Similar to MINUTE(NOW()), we can also use HOUR(NOW()) to calculate the hour at that current moment.
SELECT HOUR(NOW());
The output for the above command is 22, as the current action time is 22:16:48.
Lastly, the HOUR() function extracts the hour component from a time value. It becomes valuable when working with specific hour ranges. For instance, you can retrieve all appointments scheduled for a particular hour of the day.
MICROSECOND() Function
MySQL's MICROSECOND() function is used to get the microsecond component from a given date-time value. The output range for the microsecond function is integer values from 0 to 999999. The syntax of the MICROSECOND() function is as follows:
SELECT MICROSECOND(data-time_value)
The above command takes the date-time value as the input parameter and returns a value between 0 to 999999. Let’s see some examples to have better clarity.
Example-1:
SELECT MICROSECOND("2023-05-25 06:24:10.987654");
Output –
987654
Example-2:
SELECT MICROSECOND("2023-05-25 10:05:15.000001");
Output –
1
Unlike MINUTE(NOW()) and HOUR(NOW()), we cannot use MICROSECOND(NOW()) to calculate the microsecond at that instant because the NOW() function has second precision, so they do not include microsecond values. If we use MICROSECOND(NOW()) in MySQL, the output always will be zero.
If you specifically need the current time with microsecond precision, you can use the following alternative approach:
SELECT MICROSECOND(SYSDATE(6));
This query uses the SYSDATE(6) function to get the current date and time with microsecond precision. Then, the MICROSECOND() function extracts the microsecond value.
The MICROSECOND() function lets you extract the microsecond component from the input time value. This function is beneficial when dealing with high-precision time calculations. It enables you to perform precise measurements, comparisons, and calculations down to the microsecond level. You can use it to measure the execution time of queries, measure time intervals with extreme accuracy, or compare the performance of different algorithms.
Frequently Asked Questions
What is MICROSECOND() and MINUTE() function in MySQL?
The MICROSECOND() in MySQL extracts the microsecond component from a time value, while the MINUTE() extracts the minute component from a time value. The MICROSECOND() is used where we need high precision and MINUTE() can be used to calculate the duration between events in minutes.
What are other date and time functions in MySQL?
Some other date and time functions in MySQL include NOW(), CURDATE(), CURTIME(), DATE(), TIME(), TIMESTAMP(), DATE_FORMAT(), DATE_ADD(), DATE_SUB(), and DATEDIFF(). These functions offer various methods for manipulating and working with date and time values in MySQL.
What is the syntax for using the HOUR() function in MySQL?
The syntax for using MySQL's HOUR (date-time_value) function is HOUR(date-time_value). You provide the data time value as an input argument within the function to extract the hour component from the given time value. The output is an integer value ranging from 0 to 23.
Conclusion
We expect this article was insightful and that you learned something new today. In this blog, we learned about MINUTE(), MICROSECOND() and HOUR() functions in MySQL. These functions are used to retrieve date and time information. We also discussed its syntax and saw some examples for better understanding. Apart from their individual applications, these functions can be combined to achieve more complex tasks.
569+ registered 
