MySQL TIME_FORMAT Function

MySQL TIME_FORMAT function; In this tutorial, i am going to show you MySQL TIME_FORMAT() function with the help of examples.

MySQL TIME_FORMAT() function

MySQL TIME_FORMAT() converts a time in a formatted string using the format specifiers.

Syntax of MySQL TIME_FORMAT Function

The syntax of MySQL TIME_FORMAT Function; as follows:

TIME_FORMAT(time,format)

Parameters OF TIME_FORMAT() Function

ParameterDescription
timeThis is the first parameter and required. It is the time value you want formatted.
formatThis is a second parameter and also required. format is the format string.

Format Specifiers

Before we take examples of time_format() function. First of all, you need to know about the format specifiers. The given below following specifiers can be used to specify the return format. The format value must start with a percentage sign (%).

SpecifierDescription
%fMicroseconds (000000..999999)
%HHour (00..23)
%hHour (01..12)
%IHour (01..12)
%iMinutes, numeric (00..59)
%kHour (0..23)
%lHour (1..12)
%pAM or PM
%rTime, 12-hour (hh:mm:ss followed by AM or PM)
%SSeconds (00..59)
%sSeconds (00..59)
%TTime, 24-hour (hh:mm:ss)
%%A literal % character

Example 1 – MySQL TIME_FORMAT Function

Let’s take an example using MySQL TIME_FORMAT function; as follows:

SELECT TIME_FORMAT('12:50:40', '%r') AS 'Output';

The output of the above query is the following:

 +---------------------+
 |  Output             |
 +---------------------+
 |  12:50:40 PM        |
 +---------------------+

In the above example, we have used the %r format specifier, which is used to formats the time value in 12-hour (hh:mm:ss with AM or PM).

Example 2 – MySQL TIME_FORMAT Function with 24 Hour Time

Let’s take an second example using MySQL TIME_FORMAT Function with 24 hour time format; as follows:

SELECT TIME_FORMAT('12:50:40', '%T') AS 'Output';

The output of the above query is the following:

 +---------------------+
 |  Output             |
 +---------------------+
 |  12:50:40 PM        |
 +---------------------+ 

Example 3 – MySQL TIME_FORMAT Function Without Second Format

Let’s take an example MySQL TIME_FORMAT function without seconds in time; as follows:

SELECT TIME_FORMAT('12:50:10', '%h:%i %p') AS 'Output';

The output of the above query is the following:

 +---------------------+
 |  Output             |
 +---------------------+
 |  12:50: PM          |
 +---------------------+  

Example 4 – MySQL TIME_FORMAT Function with Fractional Seconds Format

Let’s take an example with fractional seconds in time; as follows:

SELECT TIME_FORMAT('12:50:10', '%H:%i:%s.%f') AS 'Output';

The output of the above query is the following:

 +---------------------+
 |  Output             |
 +---------------------+
 |  12:50:10.000000    |
 +---------------------+  

Conclusion

MySQL TIME_FORMAT function; In this tutorial, You have learned MySQL TIME_FORMAT() function with the help of examples.

Be First to Comment

Leave a Reply

Your email address will not be published. Required fields are marked *