MariaDB includes many functions that allow you to return time and datetime values in a variety of formats.

Two functions allow you to format the time portion using a 12 hour clock, with the AM/PM designator are DATE_FORMAT() and TIME_FORMAT().

There are many format specifiers that can be used with these functions, but only a couple that return the AM/PM designator.

The %r Format Specifier

The %r format specifier is used to format the time in 12 hour format, followed by the AM/PM designator.

Example:

SELECT DATE_FORMAT('2030-03-10 18:10:37', '%r');

Result:

+------------------------------------------+
| DATE_FORMAT('2030-03-10 18:10:37', '%r') |
+------------------------------------------+
| 06:10:37 PM                              |
+------------------------------------------+

The resulting time is displayed as if it had been formatted using the following format string: '%I:%i:%S %p'.

Obviously, whether it returns AM or PM depends on the actual time. Times before 12:00:00 return AM and times after that return PM.

Here’s what happens if we change the time to come before 12:00:00:

SELECT DATE_FORMAT('2030-03-10 06:10:37', '%r');

Result:

+------------------------------------------+
| DATE_FORMAT('2030-03-10 06:10:37', '%r') |
+------------------------------------------+
| 06:10:37 AM                              |
+------------------------------------------+

Both DATE_FORMAT() and TIME_FORMAT() accept the %r format specifier, so we can use the same format specifier with the TIME_FORMAT() function:

SELECT TIME_FORMAT('06:10:37', '%r');

Result:

+-------------------------------+
| TIME_FORMAT('06:10:37', '%r') |
+-------------------------------+
| 06:10:37 AM                   |
+-------------------------------+

TIME_FORMAT() accepts time values as well as datetime values, whereas DATE_FORMAT() only accepts date and datetime values. However, TIME_FORMAT() only accepts format specifiers for hours minutes and seconds. Seeing as %r returns hours, minutes and seconds, this is accepted too.

The %p Format Specifier

The %p format specifier is a more specific format specifier that is used solely to represent the AM/PM designator. It is normally used in conjunction with other format specifiers to return the time in a custom format.

As mentioned, the %r format specifier formats the time as if it had been formatted using the following format string: '%I:%i:%S %p'.

Let’s use that string explicitly:

SELECT TIME_FORMAT('18:10:37', '%I:%i:%S %p');

Result:

+----------------------------------------+
| TIME_FORMAT('18:10:37', '%I:%i:%S %p') |
+----------------------------------------+
| 06:10:37 PM                            |
+----------------------------------------+

So we get the same result as when using the %r format specifier.

However, one benefit of using this method is that we can construct the output in our own custom way.

For example:

SELECT TIME_FORMAT('18:10:37', '%l:%i %p');

Result:

+-------------------------------------+
| TIME_FORMAT('18:10:37', '%l:%i %p') |
+-------------------------------------+
| 6:10 PM                             |
+-------------------------------------+

Here, we used %l to return the hour portion without the leading zero. We also omitted the seconds portion of the time.

See MariaDB Format Strings for a full list of format specifiers that can be used with these functions.