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.