MySQL TIME_FORMAT() Function

The TIME_FORMAT() function in MySQL is used to format a given time expression according to a specific format. It allows you to display time values in various ways by using format specifiers.

Definition and Usage

• Purpose: Formats a time value based on a specified format.
• Syntax: TIME_FORMAT(time, format)

Syntax

TIME_FORMAT(time, format)

• time: The time or datetime value to be formatted.
• format: The format to apply to the time. The format can be a combination of various specifiers (explained below).

Format Specifiers

Here are the common format specifiers you can use:

Technical Details

• Works in: From MySQL 4.0 onwards
• Return Type: Returns a string formatted according to the specified format.

Examples

Example 1: Format time in 12-hour format with AM/PM

SELECT TIME_FORMAT("19:30:10", "%h %i %s %p");

Result:

07 30 10 PM

Explanation: The time 19:30:10 is formatted as 07 30 10 PM using a 12-hour format with AM/PM.

Example 2: Format time in 12-hour AM/PM format

SELECT TIME_FORMAT("19:30:10", "%r");

Result:

07:30:10 PM

Explanation: The time 19:30:10 is formatted as 07:30:10 PM using the 12-hour AM/PM format.

Example 3: Format time in 24-hour format

SELECT TIME_FORMAT("19:30:10", "%T");

Result:

19:30:10

Explanation: The time 19:30:10 is displayed in 24-hour format as 19:30:10.

Use Cases

• Time Formatting: Useful when you need to display time in a specific format for user-friendly presentation.
• AM/PM Conversion: Handy when you need to convert time into a 12-hour format with AM/PM for easier readability.
• Custom Time Display: Allows you to create custom time formats for reports, logs, or notifications.

Key Notes

• The TIME_FORMAT() function allows great flexibility with how time values are presented by using a combination of format specifiers.
• If the time value is NULL, the function will return NULL.
• The format specifiers should be used in a way that matches the desired output format, enabling precise control over how the time is represented.