How TIME_FORMAT() Works in MariaDB

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'