MySQL TIME_FORMAT() Function
The TIME_FORMAT() function in MySQL lets you customize how time values are displayed. You can create various time formats using special format codes.
TIME_FORMAT(): Definition and Usage
TIME_FORMAT() is very useful for presenting time data in a user-friendly and consistent way. It's commonly used in reports or applications to display times according to specific requirements. The function takes a time value and a format string as input, returning a formatted string as output.
Syntax
Syntax
TIME_FORMAT(time, format)
Parameter Values
| Parameter | Description |
|---|---|
time |
The time value to format. This is required. |
format |
A string containing format codes. See the table below. This is required. |
Format Codes
| Format Code | Description |
|---|---|
%f |
Microseconds (000000 to 999999) |
%H |
Hour (00 to 23, 24-hour format) |
%h |
Hour (01 to 12, 12-hour format) |
%I |
Hour (01 to 12, 12-hour format) |
%i |
Minutes (00 to 59) |
%p |
AM or PM |
%r |
Time in 12-hour format (hh:mm:ss AM/PM) |
%S |
Seconds (00 to 59) |
%s |
Seconds (00 to 59) |
%T |
Time in 24-hour format (hh:mm:ss) |
Examples
Formatting a Time Value
This formats the time "19:30:10" using the format codes %H (hour), %i (minutes), and %s (seconds).
Syntax
SELECT TIME_FORMAT("19:30:10", "%H %i %s");
Output
19 30 10
More Formatting Examples
These examples demonstrate different format options.
Syntax
SELECT TIME_FORMAT("19:30:10", "%h %i %s %p"); -- 12-hour format
SELECT TIME_FORMAT("19:30:10", "%r"); -- 12-hour format
SELECT TIME_FORMAT("19:30:10", "%T"); -- 24-hour format
Output
07 30 10 PM
07:30:10 PM
19:30:10