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.




Name Description
time A time value.
format Format specifiers.

Syntax Diagram:

MySQL TIME_FORMAT() Function - Syntax Diagram

MySQL Version: 8.0

Pictorial Presentation:

Pictorial Presentation of MySQL TIME_FORMAT() function


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


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


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()

Follow us on Facebook and Twitter for latest update.