In MariaDB, MAKETIME() is a built-in date and time function that returns a time value, based on the hours, minutes, and seconds provided as arguments.

Syntax

The syntax goes like this:

MAKETIME(hour,minute,second)

Example

Here’s an example:

SELECT MAKETIME(08, 30, 45);

Result:

+----------------------+
| MAKETIME(08, 30, 45) |
+----------------------+
| 08:30:45             |
+----------------------+

And another one:

SELECT MAKETIME(23, 8, 1);

Result:

+--------------------+
| MAKETIME(23, 8, 1) |
+--------------------+
| 23:08:01           |
+--------------------+

Ranges

If the hour argument is outside the range -838 to 838, the value is truncated with a warning.

If the minute or second arguments are out of the range 0 to 59, the result is null, and a warning is shown (the MariaDB documentation states that the range is 0 to 60, but this isn’t the case in my tests. Further, the MariaDB documentation also states that the range for MariaDB time values is '-838:59:59.999999' to '838:59:59.999999').

Anyway, here’s an example of a time with values that are on the upper end of their accepted range:

SELECT MAKETIME(838, 59, 59.999999);

Result:

+------------------------------+
| MAKETIME(838, 59, 59.999999) |
+------------------------------+
| 838:59:59.999999             |
+------------------------------+

These values were still within their accepted range, and so we got a valid time value.

Invalid Minutes & Seconds

Here’s what happens when we go over the accepted range for the minute and second arguments:

SELECT 
    MAKETIME(00, 60, 00),
    MAKETIME(00, 00, 60);

Result:

+----------------------+----------------------+
| MAKETIME(00, 60, 00) | MAKETIME(00, 00, 60) |
+----------------------+----------------------+
| NULL                 | NULL                 |
+----------------------+----------------------+
1 row in set, 1 warning (0.001 sec)

The result is null for both, with a warning.

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+-----------------------------------------+
| Level   | Code | Message                                 |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect seconds value: '60' |
+---------+------+-----------------------------------------+

Invalid Hours

Now let’s see what happens when we go outside the accepted range for the hour argument:

SELECT MAKETIME(900, 00, 00);

Result:

+-----------------------+
| MAKETIME(900, 00, 00) |
+-----------------------+
| 838:59:59             |
+-----------------------+
1 row in set, 1 warning (0.000 sec)

In this scenario, a time value is returned with hours, minutes, and seconds clipped to their upper range. A warning is also returned.

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1292 | Truncated incorrect time value: '900:00:00' |
+---------+------+---------------------------------------------+

Missing Argument

Calling MAKETIME() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT MAKETIME();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'MAKETIME'

And another example:

SELECT MAKETIME( 12, 1 );

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'MAKETIME'

Make a Date

Also see SEC_TO_TIME() for constructing a date value from a number of seconds.

Also see MAKEDATE() for constructing a date value from its year and day of year.