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.