MySQL - MINUTE() Function

---

The DATE, DATETIME and TIMESTAMP datatypes in MySQL are used to store the date, date and time, time stamp values respectively. Where a time stamp is a numerical value representing the number of milliseconds from '1970-01-01 00:00:01' UTC (epoch) to the specified time. MySQL provides a set of functions to manipulate these values.

The MYSQL MINUTE() function is used to retrieve and return the minutes in the given time or date time expression. This returns a numerical value ranging from 0 to 59.

Syntax

Following is the syntax of the above function –

MINUTE(time);

Where, time is the time expression from which you need to extract the minute.

Example 1

mysql> SELECT MINUTE('00:00:00 09:40:45.2300');
+----------------------------------+
| MINUTE('00:00:00 09:40:45.2300') |
+----------------------------------+
| 40                               |
+----------------------------------+
1 row in set (0.07 sec)

Example 2

Following is another example of this function –

mysql> SELECT MINUTE('00 12:38:48');
+-----------------------+
| MINUTE('00 12:38:48') |
+-----------------------+
| 38                    |
+-----------------------+
1 row in set (0.00 sec)

Example 3

We can also pass the date-time expression as an argument to this function –

mysql> SELECT MINUTE('2015-09-05 22:40:45.2300');
+------------------------------------+
| MINUTE('2015-09-05 22:40:45.2300') |
+------------------------------------+
| 40                                 |
+------------------------------------+
1 row in set (0.00 sec)

Example 4

In the following example we are retrieving the minute value from the current timestamp —

mysql> SELECT MINUTE(CURRENT_TIMESTAMP);
+---------------------------+
| MINUTE(CURRENT_TIMESTAMP) |
+---------------------------+
| 54                        |
+---------------------------+
1 row in set (0.00 sec)

Example 5

We can pass the result of the NOW() function as an argument to this function –

mysql> SELECT MINUTE(NOW());
+---------------+
| MINUTE(NOW()) |
+---------------+
| 54            |
+---------------+
1 row in set (0.00 sec)

Example 6

Suppose we have created a table named dispatches_data with 5 records in it using the following queries –

mysql> CREATE TABLE dispatches_data(
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchTimeStamp timestamp,
	Price INT,
	Location VARCHAR(255)
);
insert into dispatches_data values('Key-Board', 'Raja', TIMESTAMP('2019-05-04', '15:02:45'), 7000, 'Hyderabad');
insert into dispatches_data values('Earphones', 'Roja', TIMESTAMP('2019-06-26', '14:13:12'), 2000, 'Vishakhapatnam');
insert into dispatches_data values('Mouse', 'Puja', TIMESTAMP('2019-12-07', '07:50:37'), 3000, 'Vijayawada');
insert into dispatches_data values('Mobile', 'Vanaja' , TIMESTAMP ('2018-03-21', '16:00:45'), 9000, 'Chennai');
insert into dispatches_data values('Headset', 'Jalaja' , TIMESTAMP('2018-12-30', '10:49:27'), 6000, 'Goa');

Following query retrieves the minutes values from the DispatchTimeStamp column —

mysql> SELECT ProductName, CustomerName, DispatchTimeStamp, Price, MINUTE(DispatchTimeStamp) FROM dispatches_data;
+-------------+--------------+---------------------+-------+---------------------------+
| ProductName | CustomerName | DispatchTimeStamp   | Price | MINUTE(DispatchTimeStamp) |
+-------------+--------------+---------------------+-------+---------------------------+
| Key-Board   | Raja         | 2019-05-04 15:02:45 | 7000  |                         2 |
| Earphones   | Roja         | 2019-06-26 14:13:12 | 2000  |                        13 |
| Mouse       | Puja         | 2019-12-07 07:50:37 | 3000  |                        50 |
| Mobile      | Vanaja       | 2018-03-21 16:00:45 | 9000  |                         0 |
| Headset     | Jalaja       | 2018-12-30 10:49:27 | 6000  |                        49 |
+-------------+--------------+---------------------+-------+---------------------------+
5 rows in set (0.21 sec)

Example 7

Suppose we have created a table named SubscriberDetails with 5 records in it using the following queries –

mysql> CREATE TABLE SubscriberDetails (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	SubscriptionTimeStamp timestamp
);
insert into SubscriberDetails values('Raja', 'Premium', TimeStamp('2020-10-21 20:53:49'));
insert into SubscriberDetails values('Roja', 'Basic', TimeStamp('2020-11-26 10:13:19'));
insert into SubscriberDetails values('Puja', 'Moderate', TimeStamp('2021-03-07 05:43:20'));
insert into SubscriberDetails values('Vanaja', 'Basic', TimeStamp('2021-02-21 16:36:39'));
insert into SubscriberDetails values('Jalaja', 'Premium', TimeStamp('2021-01-30 12:45:45'));

Following query retrieves and displays the minute value from the subscription time for all the records —

mysql> SELECT SubscriberName, PackageName, SubscriptionTimeStamp, MINUTE(SubscriptionTimeStamp) FROM SubscriberDetails;
+----------------+-------------+-----------------------+-------------------------------+
| SubscriberName | PackageName | SubscriptionTimeStamp | MINUTE(SubscriptionTimeStamp) |
+----------------+-------------+-----------------------+-------------------------------+
| Ram            | Premium     | 2020-10-21 20:53:49   | 53                            |
| Rahman         | Basic       | 2020-11-26 10:13:19   | 13                            |
| Robert         | Moderate    | 2021-03-07 05:43:20   | 43                            |
| Radha          | Basic       | 2021-02-21 16:36:39   | 36                            |
| Rajiya         | Premium     | 2021-01-30 12:45:45   | 45                            |
+----------------+-------------+-----------------------+-------------------------------+
5 rows in set (0.18 sec)