Home » MySQL » MySQL date/time Functions

MySQL TIME_FORMAT() Function

MySQL | TIME_FORMAT() Function: Learn about the TIME_FORMAT() function, how it works, its usages, syntax, and examples.
Submitted by Apurva Mathur, on October 12, 2022

TIME_FORMAT() Function

The TIME_FORMAT() function presents the time in the format you want. You have to provide the time you want to format and the format in which you want to format this function will display the time in the required format.

TIME_FORMAT() Syntax

SELECT TIME_FORMAT(time, format);

TIME_FORMAT() Parameter(s)

This function takes two parameters:

• time: Foremost parameter which you have to provide is the time. Here you can write the time in any format.
• format: This is the format in which you want your time to be.
  We have the following options of format to apply with this function:
  • %f: MICROSECONDS (000000 TO 999999)
  • %H: This represents the hour range (00 to 23)
  • %h: This represents the hour range (00 to 12)
  • %I: This represents the hour range (00 to 23)
  • %i: This represents the minutes range (00 to 59)
  • %p: This represents the time in AM or PM.
  • %r: Time in 12 hour AM or PM 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)

TIME_FORMAT() Return Value

As a result, this function will display the time according to the format you have provided as a parameter.

MySQL TIME_FORMAT() Function Example 1

SELECT TIME_FORMAT("09:10:06", "%H %i %s");

MySQL TIME_FORMAT() Function Example 2

SELECT TIME_FORMAT("12:12:09.00009", "%p");

MySQL TIME_FORMAT() Function Example 3

SELECT TIME_FORMAT("12:12:09.00009", "%f %p");

MySQL TIME_FORMAT() Function Example 4

SELECT TIME_FORMAT("10:12:09.00119", "%f %p %h %s");

---