In MariaDB, TIME_FORMAT()
is a built-in date and time function that formats a time value according to the given format string.
It works similar to the DATE_FORMAT()
function, except that the format string may only contain format specifiers for hours, minutes, and seconds.
It requires two arguments; the time and the format string.
Syntax
The syntax goes like this:
TIME_FORMAT(time,format)
Where time
is the time value and format
is the format string. See MariaDB Format Strings for a list of acceptable format specifiers. As mentioned, only the format specifiers for hours, minutes, and seconds are accepted. All other values return null
.
Example
Here’s an example:
SELECT TIME_FORMAT('10:30:45', '%H %i %S');
Result:
+-------------------------------------+ | TIME_FORMAT('10:30:45', '%H %i %S') | +-------------------------------------+ | 10 30 45 | +-------------------------------------+
Here it is again, but this time we provide a more elaborate format string:
SELECT TIME_FORMAT(
'10:30:45',
'%H hours, %i minutes, and %S seconds'
)
AS Result;
Result:
+--------------------------------------+ | Result | +--------------------------------------+ | 10 hours, 30 minutes, and 45 seconds | +--------------------------------------+
Larger Hours
TIME
values can be in the range '-838:59:59.999999'
to '838:59:59.999999'
.
Therefore, the hour portion can be much higher than 23
:
SELECT TIME_FORMAT(
'810:30:45',
'%H hours, %i minutes, and %S seconds'
)
AS Result;
Result:
+---------------------------------------+ | Result | +---------------------------------------+ | 810 hours, 30 minutes, and 45 seconds | +---------------------------------------+
Negative Time Values
Here’s an example with a negative time value:
SELECT TIME_FORMAT(
'-810:30:45',
'%H hours, %i minutes, and %S seconds'
)
AS Result;
Result:
+----------------------------------------+ | Result | +----------------------------------------+ | -810 hours, 30 minutes, and 45 seconds | +----------------------------------------+
Out of Range Times
However, providing a time value outside the range will return the upper boundary of that range, along with a warning:
SELECT TIME_FORMAT(
'910:30:45',
'%H hours, %i minutes, and %S seconds'
)
AS Result;
Result:
+---------------------------------------+ | Result | +---------------------------------------+ | 838 hours, 59 minutes, and 59 seconds | +---------------------------------------+ 1 row in set, 1 warning (0.003 sec)
Let’s check the warning:
SHOW WARNINGS;
Result:
+---------+------+---------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------+ | Warning | 1292 | Truncated incorrect time value: '910:30:45' | +---------+------+---------------------------------------------+
Invalid Arguments
When passed any invalid arguments, TIME_FORMAT()
returns null
with a warning:
SELECT TIME_FORMAT('Homer', 'Simpson');
Result:
+---------------------------------+ | TIME_FORMAT('Homer', 'Simpson') | +---------------------------------+ | NULL | +---------------------------------+ 1 row in set, 1 warning (0.009 sec)
Check the warning:
SHOW WARNINGS;
Result:
+---------+------+-------------------------------+ | Level | Code | Message | +---------+------+-------------------------------+ | Warning | 1292 | Incorrect time value: 'Homer' | +---------+------+-------------------------------+
Invalid Format String
The format string may only contain format specifiers for hours, minutes, and seconds. Any other format specifiers result in null
being returned:
SELECT TIME_FORMAT('10:30:45', '%M %W');
Result:
+----------------------------------+ | TIME_FORMAT('10:30:45', '%M %W') | +----------------------------------+ | NULL | +----------------------------------+
Missing Argument
Calling TIME_FORMAT()
with the wrong number of arguments, or without passing any arguments, results in an error:
SELECT TIME_FORMAT();
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TIME_FORMAT'
And another example:
SELECT TIME_FORMAT('10:09:10');
Result:
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'TIME_FORMAT'