Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
MINUTE() Function
3.
HOUR() Function
4.
MICROSECOND() Function
5.
Frequently Asked Questions
5.1.
What is MICROSECOND() and MINUTE() function in MySQL?
5.2.
What are other date and time functions in MySQL?
5.3.
What is the syntax for using the HOUR() function in MySQL?
6.
Conclusion
Last Updated: Mar 27, 2024
Easy

MINUTE(), MICROSECOND() and HOUR() Functions in MySQL

Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

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.

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 –

Output for SELECT MINUTE(NOW())

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.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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());
Output for 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));
Output for SELECT MICROSECOND(NOW())

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.

If you want to learn more about MySQL, do visit.

You may refer to our Guided Path on Code Studios for enhancing your skill set on DSACompetitive ProgrammingSystem Design, etc. Check out essential interview questions, practice our available mock tests, look at the interview bundle for interview preparations, and so much more!

Happy Learning, Ninja!

Live masterclass