How the MINUTE() function works in Mariadb?

The MINUTE() function is a useful tool for extracting the minute part of a time or datetime value.

Posted on

The MINUTE() function is a useful tool for extracting the minute part of a time or datetime value. It can be used for various purposes, such as formatting, calculating, and comparing time values.

Syntax

The syntax of the MINUTE() function is as follows:

MINUTE(time)

The function takes one argument:

  • time: A time or datetime value that represents the input to extract the minute part from. It can be any valid expression that returns a time or datetime value, such as a column name, a literal, or a function.

The function returns an integer value that represents the minute part of the input time or datetime value, ranging from 0 to 59.

Examples

In this section, we will show some examples of how to use the MINUTE() function in different scenarios.

Example 1: Extracting the minute part of a literal time value

Suppose you want to extract the minute part of a literal time value, such as ‘12:34:56’. You can use the MINUTE() function to do so. For example, you can execute the following statement:

SELECT MINUTE('12:34:56');

This will return the minute part of the time value, which is 34. For example, the result might look like this:

+--------------------+
| MINUTE('12:34:56') |
+--------------------+
|                 34 |
+--------------------+

Note that the minute part is the second value of the time value, separated by a colon. For example, the minute part of the time value ‘12:00:00’ is 0, not 12.

Example 2: Extracting the minute part of a column value

Suppose you have a table called events that stores the information of various events, such as their name, start_time, and end_time. The start_time and end_time columns are datetime values that represent the start and end time of the event. You want to extract the minute part of the start_time and end_time of each event, so that you can format, calculate, or compare the time values. You can use the MINUTE() function to do so. For example, you can execute the following statement:

SELECT name, start_time, end_time,
MINUTE(start_time) AS start_minute,
MINUTE(end_time) AS end_minute FROM events;

This will return the name, start_time, end_time, and the minute part of the start_time and end_time of each event, or an empty result set if the table is empty. For example, the result might look like this:

+----------+---------------------+---------------------+--------------+------------+
| name     | start_time          | end_time            | start_minute | end_minute |
+----------+---------------------+---------------------+--------------+------------+
| Event A  | 2024-01-01 10:00:00 | 2024-01-01 11:00:00 | 0            | 0          |
| Event B  | 2024-01-02 12:15:00 | 2024-01-02 13:30:00 | 15           | 30         |
| Event C  | 2024-01-03 14:45:00 | 2024-01-03 15:15:00 | 45           | 15         |
+----------+---------------------+---------------------+--------------+------------+

Note that the minute part is the second value of the datetime value, separated by a colon. For example, the minute part of the start_time of Event B is 15, not 12.

Example 3: Extracting the minute part of a function result

Suppose you want to extract the minute part of the result of a function, such as NOW() or CURTIME(). You can use the MINUTE() function to do so. For example, you can execute the following statement:

SELECT MINUTE(NOW());

This will return the minute part of the current date and time, which is a dynamic value that changes every minute. For example, the result might look like this:

+---------------+
| MINUTE(NOW()) |
+---------------+
|            45 |
+---------------+

Similarly, you can execute the following statement:

SELECT MINUTE(CURTIME());

This will return the minute part of the current time, which is a dynamic value that changes every minute. For example, the result might look like this:

+-------------------+
| MINUTE(CURTIME()) |
+-------------------+
|                45 |
+-------------------+

There are some other functions that are related to the MINUTE() function and can be used to perform other time operations in Mariadb. Here are some of them:

  • SECOND(): This function returns the second part of a time or datetime value, ranging from 0 to 59.
  • HOUR(): This function returns the hour part of a time or datetime value, ranging from 0 to 23.
  • DAY(): This function returns the day part of a date or datetime value, ranging from 1 to 31.
  • MONTH(): This function returns the month part of a date or datetime value, ranging from 1 to 12.
  • YEAR(): This function returns the year part of a date or datetime value, ranging from 1000 to 9999.
  • MICROSECOND(): This function returns the microsecond part of a time or datetime value, ranging from 0 to 999999.

Conclusion

The MINUTE() function is a powerful and flexible function that can help you extract the minute part of a time or datetime value. It can be used for various purposes, such as formatting, calculating, and comparing time values. You can also use some other related functions to extract other time parts, such as second, hour, day, month, year, or microsecond. By using these functions, you can achieve a better analysis and understanding of your time data.