MySQL MAKETIME()

By /
MAKETIME Function

In this tutorial, we will study the MySQL MAKETIME() function. Suppose you have individual values of hour, minute and seconds values and you need to enter these values in a table such that it is a time value. This is where MySQL MAKETIME() is useful. The MAKETIME() function  is used to make a time value. It takes three arguments – an hour, a minute and a second value and then creates and returns a time value in the HH:MM:SS format. So for instance if you pass 12 as hour value, 34 as minute value and 45 as seconds value, MAKETIME() will return the time as 12:34:45. Let us dive into the function’s syntax and examples.

Recommended read – MySQL TIMEDIFF() and MySQL DATEDIFF()

Syntax of MySQL MAKETIME()

MAKETIME(hour, minute, second)Code language: SQL (Structured Query Language) (sql)

Where, ‘hour’ specifies the hour value in the time, ‘minute’ specifies the minute value in the time and ‘second’ specifies the second value.

Examples of MySQL MAKETIME()

Let us start by looking at a few basic examples of MySQL MAKETIME(). Consider the below queries. We will use the SELECT statement and an alias called ‘Time’ to make our output more readable.

SELECT MAKETIME(12,45,03) AS Time; 
SELECT MAKETIME(20,06,43) AS Time;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL MAKETIME Basic Examples

MySQL MAKETIME() With Larger Hour Values

We can pass a large hour value (greater than 24) as well to the MAKETIME() function. Let us see two examples where we pass 160 and 423 as hours values respectively. Consider the below queries.

SELECT MAKETIME(160,45,12) AS Time; 
SELECT MAKETIME(423,59,59) AS Time;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL MAKETIME Large Values

MySQL MAKETIME() With Wrong Values

What if you pass values that will result in invalid time values? For instance, what if we pass a minute or second value greater than 59? MySQL MAKETIME() returns NULL if we pass invalid minute and second values. Let us see an example of this. Consider the below queries. 

SELECT MAKETIME(07,60,45) AS Time; 
SELECT MAKETIME(07,23,72) AS Time;Code language: SQL (Structured Query Language) (sql)

And the output is –

MySQL MAKETIME Wrong Values

Limitation of the Time Data Type

One thing you should know while working with the time data type is that it has a major limitation. The time data type is limited to a range from -838:59:59 to 838:59:59. This means it does not recognize values out of that range. Let us pass 900 as the hour value and see what MAKETIME() outputs. Consider the below query.

SELECT MAKETIME(900,23,72) AS Time;Code language: SQL (Structured Query Language) (sql)

And the output is –

Maketime Limitation

MAKETIME() will return NULL if the resulting time value is outside the range – 838:59:59 to 838:59:59. 

MAKETIME() With Fractional Seconds Precision

What if you want fractional seconds precision in your resulting time value from MAKETIME()? Well, the below query will show you how you can do it. Simply add the fractional seconds part along with the seconds.

SELECT MAKETIME(12,45,03.51367) AS Time; 
SELECT MAKETIME(07,23,12.126) AS Time;Code language: SQL (Structured Query Language) (sql)

And the output is –

Maketime Fsp

Working With Tables

Finally, let us see an example of MAKETIME() with tables. Consider the below Shops table.

Maketime Shops Table
Shops Table

Your job is to create a column called ‘OpeningTime’ which stores the opening time of the corresponding shops as follows.

  • Shop with ID 1 opens at 7 hours, 30 minutes and 15 seconds.
  • Shop with ID 2 opens at 6 hours, 30 minutes and 0 seconds.
  • Shop with ID 3 opens at 10 hours, 15 minutes and 40 seconds.
  • Shop with ID 4 opens at 5 hours, 0 minutes and 23 seconds.
  • Shop with ID 5 opens at 11 hours, 30 minutes and 13 seconds.
  • Shop with ID 6 opens at 12 hours, 05 minutes and 06 seconds.
  • Shop with ID 7 opens at 9 hours, 20 minutes and 30 seconds.

Let us use the ALTER statement, UPDATE statement and the MAKETIME() function to add the column and these values in the Shops table. We will use the ALTER statement to add the ‘OpeningTime’ column of the time data type. And then we will use the UPDATE statement to add the values. The queries are –

ALTER TABLE Shops ADD OpeningTime time; 
UPDATE Shops SET OpeningTime=MAKETIME(07,30,15) WHERE ID=1; 
UPDATE Shops SET OpeningTime=MAKETIME(06,30,00) WHERE ID=2; 
UPDATE Shops SET OpeningTime=MAKETIME(10,15,40) WHERE ID=3; 
UPDATE Shops SET OpeningTime=MAKETIME(05,00,23) WHERE ID=4; 
UPDATE Shops SET OpeningTime=MAKETIME(11,30,13) WHERE ID=5;
UPDATE Shops SET OpeningTime=MAKETIME(12,05,06) WHERE ID=6; 
UPDATE Shops SET OpeningTime=MAKETIME(09,20,30) WHERE ID=7; 
SELECT * FROM Shops;Code language: SQL (Structured Query Language) (sql)

And the output is –

Maketime Table Example1

Conclusion

The MAKETIME() function is very useful. It helps you to make time values by passing the hour, minute and second values individually. I would encourage you to practice some more queries with this function.

References