MySQL | TIME_FORMAT method
Start your free 7-days trial now!
MySQL's TIME_FORMAT(~) method will return a time formatted according to the provided format.
Parameters
1. time | time
The time to format.
2. format | string
The format to use for formatting.
Refer to 'List of formatting types' at the bottom of page.
Return value
Returns the formatted time.
Examples
Basic usage
To format the input time as 12-hour hh:mm:ss followed by AM or PM:
SELECT TIME_FORMAT('23:32:40', '%r');
+-------------------------------+| TIME_FORMAT('23:32:40', '%r') |+-------------------------------+| 11:32:40 PM |+-------------------------------+
The inputted 24-hour time has been returned as a 12-hour time with AM or PM.
Hour formatting
To compare the different types of formatting available for the hour portion 25:
SELECT TIME_FORMAT('25:00:00', '%H %k %h %I %l');
+-------------------------------------------+| TIME_FORMAT('25:00:00', '%H %k %h %I %l') |+-------------------------------------------+| 25 25 01 01 1 |+-------------------------------------------+
Note here that:
%Hand%khour format specifiers can produce values larger than 23. We see they return25here.%h,%I, and%lhour format specifiers produce the hour value modulo 12. We see they return01,01and1respectively here.
List of formatting types
Below is a table with a list of valid formatting types:
Specifier | Description |
|---|---|
| Microseconds (000000..999999) |
| Hour (00..23). Can produce value above 23 if required. |
| Hour (01..12). For values greater than 12, the hour value modulo 12. |
| Hour (01..12). For values greater than 12, the hour value modulo 12. |
| Minutes, numeric (00..59) |
| Hour (0..23). Can produce value above 23 if required. |
| Hour (1..12). For values greater than 12, the hour value modulo 12. |
| AM or PM |
| Time, 12-hour (hh:mm:ss followed by AM or PM) |
| Seconds (00..59) |
| Seconds (00..59) |
| Time, 24-hour (hh:mm:ss) |
