MySQL - MAKETIME() 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 MAKETIME() function accepts three numerical values representing hour, minute and second values as parameters (in the same order), creates a time value based on these values, and returns the result.

Syntax

Following is the syntax of the above function –

MAKETIME(hour,minute,second)

Example 1

Following example demonstrates the usage of the MAKETIME() function –

mysql> SELECT MAKETIME(13, 45, 25);
+----------------------+
| MAKETIME(13, 45, 25) |
+----------------------+
| 13:45:25             |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(22, 25, 58);
+----------------------+
| MAKETIME(22, 25, 58) |
+----------------------+
| 22:25:58             |
+----------------------+
1 row in set (0.00 sec)

Example 2

Following is another example of this function –

mysql> SELECT MAKETIME(05, 20, 50);
+----------------------+
| MAKETIME(05, 20, 50) |
+----------------------+
| 05:20:50             |
+----------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(12, 23, 30);
+----------------------+
| MAKETIME(12, 23, 30) |
+----------------------+
| 12:23:30             |
+----------------------+
1 row in set (0.00 sec)

Example 3

You can also pass arguments to this function as string values.

mysql> SELECT MAKETIME('09', '21', '34');
+----------------------------+
| MAKETIME('09', '21', '34') |
+----------------------------+
| 09:21:34.000000            |
+----------------------------+
1 row in set (0.00 sec)

Example 4

Any of the arguments of this function can be 0.

mysql> SELECT MAKETIME(23, 24, 0);
+---------------------+
| MAKETIME(23, 24, 0) |
+---------------------+
| 23:24:00            |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(23, 0, 55);
+---------------------+
| MAKETIME(23, 0, 55) |
+---------------------+
| 23:00:55            |
+---------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(0, 24, 55);
+---------------------+
| MAKETIME(0, 24, 55) |
+---------------------+
| 00:24:55            |
+---------------------+
1 row in set (0.00 sec)

Example 5

If either of the arguments of this function is NULL it returns NULL.

mysql> SELECT MAKETIME(23, 24, NULL);
+------------------------+
| MAKETIME(23, 24, NULL) |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(NULL, 24, 55);
+------------------------+
| MAKETIME(NULL, 24, 55) |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)
mysql> SELECT MAKETIME(23, NULL, 55);
+------------------------+
| MAKETIME(23, NULL, 55) |
+------------------------+
| NULL                   |
+------------------------+
1 row in set (0.00 sec)

Example 6

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

mysql> CREATE TABLE SalesInfo (
	ProductName VARCHAR(255),
	CustomerName VARCHAR(255),
	DispatchHour INT,
	DispatchMinute INT,
	DispatchSecond INT,
	Price INT,
	Location VARCHAR(255)
);
insert into SalesInfo values('Key-Board', 'Raja', 15, 02, 45, 7000, 'Hyderabad');
insert into SalesInfo values('Earphones', 'Roja', 14, 13, 12, 2000, 'Vishakhapatnam');
insert into SalesInfo values('Mouse', 'Puja', 07, 50, 37, 3000, 'Vijayawada');
insert into SalesInfo values('Mobile', 'Vanaja', 16, 00, 45, 9000, 'Chennai');
insert into SalesInfo values('Headset', 'Jalaja', 10, 49, 27, 6000, 'Goa');

Following query retrieves the time values from the DispatchHour, DispatchMinuts and, DispatchSecond columns —

mysql> SELECT ProductName, CustomerName, Price, DispatchHour, DispatchMinute, DispatchSecond, MAKETIME(DispatchHour, DispatchMinute, DispatchSecond) as DispatchTime FROM SalesInfo;
+-------------+--------------+-------+--------------+----------------+----------------+--------------+
| ProductName | CustomerName | Price | DispatchHour | DispatchMinute | DispatchSecond | DispatchTime |
+-------------+--------------+-------+--------------+----------------+----------------+--------------+
| Key-Board   | Raja         | 7000  | 15           | 2              | 45             | 15:02:45     |
| Earphones   | Roja         | 2000  | 14           | 13             | 12             | 14:13:12     |
| Mouse       | Puja         | 3000  | 7            | 50             | 37             | 07:50:37     |
| Mobile      | Vanaja       | 9000  | 16           | 0              | 45             | 16:00:45     |
| Headset     | Jalaja       | 6000  | 10           | 49             | 27             | 10:49:27     |
+-------------+--------------+-------+--------------+----------------+----------------+--------------+
5 rows in set (0.00 sec)

Example 7

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

mysql> CREATE TABLE Subscriber_Info (
	SubscriberName VARCHAR(255),
	PackageName VARCHAR(255),
	Hour INT,
	Minute INT,
	Second INT
);
insert into Subscriber_Info values('Raja', 'Premium', 20, 53, 49);
insert into Subscriber_Info values('Roja', 'Basic', 10, 13, 19);
insert into Subscriber_Info values('Puja', 'Moderate', 05, 43, 20);
insert into Subscriber_Info values('Vanaja', 'Basic', 16, 36, 39);
insert into Subscriber_Info values('Jalaja', 'Premium', 12, 45, 45);

In the following query we are passing column names Hour, Minute and Year as arguments to the MAKETIME() function and trying to retrieve the subscription time.

mysql> SELECT SubscriberName, PackageName, MAKETIME(Hour, Minute, Second) as SubscriptionTime FROM Subscriber_Info;
+----------------+-------------+------------------+
| SubscriberName | PackageName | SubscriptionTime |
+----------------+-------------+------------------+
| Raja           | Premium     | 20:53:49         |
| Roja           | Basic       | 10:13:19         |
| Puja           | Moderate    | 05:43:20         |
| Vanaja         | Basic       | 16:36:39         |
| Jalaja         | Premium     | 12:45:45         |
+----------------+-------------+------------------+
5 rows in set (0.23 sec)