MySQL TIME_FORMAT() function

TIME_FORMAT() function

MySQL TIME_FORMAT() converts a time in a formatted string using the format specifiers. It allows you to present time values in a custom and human-readable format, making it useful for various scenarios.

The format specifiers used in DATE_FORMAT() may also use with this function but other specifiers than hours,minutes, seconds and microseconds produce a NULL value or 0.

This function is useful in -

• TIME_FORMAT() is valuable for presenting time-based data with a customized appearance in reports and user interfaces.
• The function is used in applications where time values need to be displayed to users in a specific format.
• TIME_FORMAT() helps in creating custom time labels for various time-related data, enhancing data clarity.
• It provides a way to display time values in a format that is more understandable and user-friendly.
• The function is suitable for international applications as it allows you to specify custom time formats that adhere to different regional conventions.
• It supports time-based analysis by presenting time values in a consistent format for comparisons and calculations.
• TIME_FORMAT() can be used to format time durations or intervals in a human-readable manner.

Syntax:

TIME_FORMAT(time,format);

Arguments:

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example:

The following statement will convert a given time string 97:15:40 into %H %k %h %I %l format.

Code:

SELECT TIME_FORMAT('97:15:40','%H %k %h %I %l');

Output:

mysql> SELECT TIME_FORMAT('97:15:40','%H %k %h %I %l');
+------------------------------------------+
| TIME_FORMAT('97:15:40','%H %k %h %I %l') |
+------------------------------------------+
| 97 97 01 01 1                            | 
+------------------------------------------+
1 row in set (0.01 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: SYSDATE()
Next: TIME_TO_SEC()