Show time with AM/PM indicator in MariaDB

MariaDB has many built-in functions that can return time and date values ​​in a specified format, DATE_FORMAT() and TIME_FORMAT() functions are the most commonly used.

To use an AM/PM indicator for a time value, you need to format the time value using a 12-hour clock. Combining several format specifiers, you can easily display AM/PM indicators.

The %p format specifier

The %p format specifier is a more specific format specifier used only for AM/PM specifiers. It is often used in conjunction with other format specifiers to return the time in a custom format.

The %r format specifier formats the time as if it had been formatted with the following format string: '%I:%i:%S %p'.

Let’s use that string explicitly:

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

Output:

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

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

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

E.g:

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

Output:

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

Here we use %l to return the hour part without leading zeros. We also omit the seconds part of the time.

The %r format specifier

The %r format specifier is used to format the time in 12-hour format followed by the AM/PM designator. The %r format specifier is equivalent to '%I:%i:%S %p'.

The following statement use DATE_FORMAT() function with the %r format specifier to format 2022-12-06 18:10:37:

SELECT DATE_FORMAT('2022-12-06 18:10:37', '%r');

Output:

+------------------------------------------+
| DATE_FORMAT('2022-12-06 18:10:37', '%r') |
+------------------------------------------+
| 08:10:37 PM                              |
+------------------------------------------+

Of course, the AM or PM depends on the actual time. Returns AM if the time was before 12:00:00, otherwise returns PM.

Let’s take a look at this example with the time before 12:00:00:

SELECT DATE_FORMAT('2022-12-06 08:10:37', '%r');

Output:

+------------------------------------------+
| DATE_FORMAT('2022-12-06 08:10:37', '%r') |
+------------------------------------------+
| 08:10:37 AM                              |
+------------------------------------------+

Like the DATE_FORMAT() function, the %r format specifier is also used in TIME_FORMAT(), as in the following example:

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

Output:

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

The TIME_FORMAT() function accepts both time and datetime values, while DATE_FORMAT() accepts date and datetime values. However, TIME_FORMAT() accepts format specifiers about hour, minute and second.

Conclusion

This article illustrates several ways to add AM/PM to time values ​​in MariaDB:

• Use the DATE_FORMAT() or TIME_FORMAT() function to format time values.
• Use the %p format specifier to output the AM/PM designator.
• Use the %r format specifier to output the time with AM/PM designators.